#####################################################################
# Product:      KnowledgeEngine (Plone Edition)
#
# File:         MySQLFiveAssessmentStorageService.py
#
# Copyright:    Copyright (c) 2005, Faulkner Technologies
#
# Author:       Brian Oliver, James Davies
#
# Description:  Defines an assessment storage service,
#               implementation based on the MySQL database
#
#               NOTE: sql commands to create the appropriate
#               mysql database for this storage service
#               are located in the folder containing this file.
#
# See Also:     Assessment, Assessible, AssessmentStorageService
#               AssessmentComponentState, AssessmentAttribute,
#               AssessmentComponentValueTypes
#####################################################################

#####################################################################
## KnowledgeEngine Library Imports
from Products.KnowledgeEngine.Common import *
from Products.KnowledgeEngine.Core import *
from Products.KnowledgeEngine.Assessment import Assessment
from Products.KnowledgeEngine.AssessmentAttribute import AssessmentAttribute
from Products.KnowledgeEngine.AssessmentAttributeHandle import AssessmentAttributeHandle
from Products.KnowledgeEngine.AssessmentComponentState import AssessmentComponentState
from Products.KnowledgeEngine.AssessmentComponentValueTypes import *
from Products.KnowledgeEngine.AssessmentStorageService import AssessmentStorageServiceInfo, AssessmentStorageService
from Products.KnowledgeEngine.KERLProcessor import *

from Products.KnowledgeEngine.PropertyDefinition import ValidationException

from Products.KnowledgeEngine.Utils.CallbackIterator import CallbackIterator, buildDict
from Products.KnowledgeEngine.Utils.OrderedDict import OrderedDict

from Products.KnowledgeEngine.MySQLMixin import MySQLMixin, qstr, qlist, qobject

#####################################################################
## Zope Library Imports
from Globals import DTMLFile
from AccessControl import ClassSecurityInfo, Unauthorized
from ImageFile import ImageFile
from DateTime import DateTime
from Products.PageTemplates.PageTemplateFile import PageTemplateFile 

#####################################################################
## Python Library Imports
import re
import sys
import time
import types
from cStringIO import StringIO

#####################################################################
## MySQL Library imports
import MySQLdb
from _mysql import OperationalError

#####################################################################
## Python Logging Configuration
import logging
log = logging.getLogger("KnowledgeEngine.StorageService")

#---------------------------------------------------------------#

#MYSQL Constants
DATETIME_FORMAT = "%Y-%m-%d %H:%M:%S"



    

#####################################################################
## MySQLFiveAssessmentStorageServiceInfo Class Definition
class MySQLFiveAssessmentStorageServiceInfo(AssessmentStorageServiceInfo):
    """
        Defines the assessment storage service meta-information
    """
    
    #---------------------------------------------------------------#
    
    def getClassName(self):
        """
            Returns the name of the class for which 
            we are providing information.
        """
        return 'MySQLFiveAssessmentStorageService'


    #---------------------------------------------------------------#

    def getDisplayName(self):
        """
            Return a friendly name for the class instances
            (this is used for displaying in lists etc)
        """
    
        return "KnowledgeEngine: MySQL Five Assessment Storage Service"

#####################################################################
## MySQLFiveAssessmentStorageService Class Definition
class MySQLFiveAssessmentStorageService(AssessmentStorageService, MySQLMixin):
    """
        Defines a tool that may be used to manage the storage of 
        assessments.
    """

    security = ClassSecurityInfo()
    
    security.setDefaultAccess('allow')

    manage_options=(
        (
            { 'label':'Properties', 'action':'manage_propertiesForm' },
            { 'label':'Setup',      'action':'manage_wizard'         },
        ))


    manage_wizard = PageTemplateFile("skins/assessmentstorageservice5_wizard",globals())

    #---------------------------------------------------------------#        

    security.declareProtected('Manage portal','manage_setConnectionId')
    def manage_setConnectionId(self, connectionId, REQUEST=None):
        """ 
            Sets the connection id 
        """
        self.manage_changeProperties(dbConnectionName=connectionId) 

        if REQUEST:
            return self.REQUEST.RESPONSE.redirect('manage_wizard')
                
    #---------------------------------------------------------------#                        
                                       
    
    security.declarePrivate('insert')
    def insert(self, query, args=None):
        """
            Execute the specified INSERT/UPDATE/DELETE Query.
            Returns the insert_id, if any
        """
 
        connection = self.getDatabaseConnection()
        connection.query( query )
        return connection.db.insert_id()

    #---------------------------------------------------------------#        

    security.declarePrivate('executeFilenae')
    def executeFilename(self, filename):
        """ 
            Executes all SQL Queries in the specified filename "
        """
                
        blob = "".join(open(filename).xreadlines())
        queries = blob.split(';')
        
        for query in queries:
            self.execute( query )
        
    #---------------------------------------------------------------#                
        
    security.declareProtected('Manage portal','initdatabase')        
    def initDatabase(self, REQUEST=None):
        """ 
            Initializes the database 
        """
        
        thisfile = globals()['__file__']
        basename = "/".join( thisfile.split('/')[:-1] )
        
        if self.getDatabaseVersion() != 0.0:
            raise "Original database is not version 0.0"

        # self.executeFilename( basename + '/mysql.sql' )
        self.performDbMigrations()
        
        if REQUEST:
            return REQUEST.RESPONSE.redirect('manage_wizard')        
        
    #---------------------------------------------------------------#        
        
    def performDbMigrations(self, REQUEST=None):
        """
            Iterate through the SQL migrations executing each in order
        """

        thisfile = globals()['__file__']
        basename = "/".join( thisfile.split('/')[:-1] )

        # currentSchemaVersion = self.getDatabaseSchemaVersion()
        files = os.listdir(basename + "/migrations")
        files.sort()

        schemaVersion = self.getDatabaseSchemaVersion()
        updatedVersion = schemaVersion
        regexp=re.compile("^(\d{3})_([^\.]*).sql")
        for file in files:
            reResult = regexp.search(file)
            if reResult:
                mVersion, name = reResult.groups()
                mVersion = int(mVersion)
                if mVersion > schemaVersion:
                    self.executeFilename( basename + "/migrations/" + file)
                    updatedVersion = mVersion
        
        # Update the schema_info database if we need to
        if updatedVersion > schemaVersion:
            self.sqlQuery('UPDATE schema_info SET version=%d' % updatedVersion )

        if REQUEST:
            return REQUEST.RESPONSE.redirect('manage_wizard')        

    #---------------------------------------------------------------#        
        
    security.declareProtected('Manage portal','initdatabase')        
    def upgradeDatabase(self, REQUEST=None):
        """ Upgrades the database to KE4 """
        
        thisfile = globals()['__file__']
        basename = "/".join( thisfile.split('/')[:-1] )
        
        dbVersion = self.getDatabaseVersion()
        if dbVersion == 3.0:
            self.executeFilename( basename + '/migrate.sql' )
        
        if dbVersion < 4.2:
            self.executeFilename( basename + '/migrate_4.x_4.3.sql' )

        # Execute any db migrations
        self.performDbMigrations()
        
        if REQUEST:
            return REQUEST.RESPONSE.redirect('manage_wizard')

    #---------------------------------------------------------------#        

    security.declareProtected('Manage portal','getDatabaseVersion')
    def getDatabaseSchemaVersion(self):
        """
            Returns the current database schema version from the
            schema_info table
        """

        result = []
        try:
            result = self.sqlQuery("SELECT DISTINCT version FROM schema_info")
        except:
            pass

        if len(result):
            return result[0]['version']

        return -1

    #---------------------------------------------------------------#        

    security.declareProtected('Manage portal','getDatabaseVersion')
    def getDatabaseVersion(self):
        """
            Attempts to guess whether the currently active database is 
            version 3 or 4. 
        """    
        try:
            connection = self.getDatabaseConnection()
        except AttributeError:
            return None

        description, rs = connection.query("SHOW TABLES")
        tables = [row[0] for row in rs]
        
        if tables:
            if 'answers' in tables:
                if 'assessmentRevisions' in tables:
                    return 4.0
                else:
                    if 'schema_info' in tables:
                        return 4.3
                    else:
                        return 4.2
            elif 'strings' in tables:
                return 3.0

        return 0.0 # Not fully initialized

    #---------------------------------------------------------------#
    
    security.declarePublic('getAssessmentHistory')
    def getAssessmentHistory( self, assessmentIdentity ):
        """
            Calculates the delta for each revision of an assessment 
            and returns in a data structure suitable for rendering in Page Templates.
        """
        
        sql = """
            SELECT a.revision, componentIdentity, a.instantModified, value, valueType, user
            FROM answers a
            LEFT OUTER JOIN assessmentRevisions ar
                ON ar.revision = a.revision and ar.assessmentIdentity = a.assessmentIdentity
            WHERE a.assessmentIdentity = %s 
            ORDER BY a.revision ASC
            """ % qstr(assessmentIdentity)
            
        
        rs = self.execute( sql )                    
        
        # Use an ordered dictionary so revisions come out in the same order we
        # process them.
        revisions = OrderedDict() 
        lastValues = {}
        
        # Process each revision
        for row in rs:
            brain = {}
            
            revision            = row[0]
            componentIdentity   = row[1]
            
            # Extract the values from the row
            brain['componentIdentity']   = componentIdentity
            brain['revision']            = revision
            brain['instantModified']     = row[2]
            brain['value']               = row[3]
            brain['valueType']           = row[4]
            brain['user']                = row[5]
            
            
            brain['oldValue'] = lastValues.get(componentIdentity, None)
                            
            #print "lastValues: " + `lastValues`
            
            lastValues[componentIdentity] = row[3]

            #brain['oldValue'] = None

            if revision not in revisions:
                revisions[revision] = {
                    'revision': revision,
                    'when': DateTime(row[2]),
                    'user': row[5],
                    'components': { componentIdentity: brain }
                }
            else:
                revisions[revision]['components'][componentIdentity] = brain
            
        # Sort in reverse chronological order.
        revisions.reverse()
            
        return revisions         
            
    
    
    #---------------------------------------------------------------#
    
    security.declarePrivate('getLatestAssessmentRevision')
    def getLatestAssessmentRevision( self, assessmentIdentity ):
        """ Returns the latest revision for the specified assessment """
        
        sql = "SELECT latestRevision FROM assessments WHERE assessmentIdentity = %s" % qstr(assessmentIdentity)
        resultSet = self.sqlQuery(sql)
        if len(resultSet):
            return resultSet[0]['latestRevision']
        else:
            return 0
        
        
    
    #---------------------------------------------------------------#
    

    #ABSTRACT TRANSIENT: a ServiceInfo implementation instance to provide info about this service
    _SERVICEINFO = MySQLFiveAssessmentStorageServiceInfo()

    #---------------------------------------------------------------#

    def __init__(self):
        """
            Constructor for the object
        """
                
        #perform default initialisations
        AssessmentStorageService.__init__(self)
        
        #create the default properties (just the name of the mysql connection object)
        self._setProperty('dbConnectionName', '', 'string')


    #---------------------------------------------------------------#

    security.declarePrivate('getDatabaseConnection')
    def getDatabaseConnection(self):
        """
            PRIVATE: Return a Zope Data Adapter (Connection object) to be
            used by this assessment storage service
        """

        starttime = time.clock()

        #attempt to locate the Zope Database Connection 
        zda = getattr(self, self.dbConnectionName)

        #get and return a real connection from the zda
        connection = zda()

        endtime = time.clock()

        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, "Fetched Database Connection in %f" % (endtime-starttime)))        
        
        return connection
        
    #---------------------------------------------------------------#
    
    security.declarePublic('uniqueValuesFor')
    def uniqueValuesFor(self, searchIndex, modelIdentities=None, limit=None):
        """ Returns a list of all unique values for the specified search index """
                
        # TODO: Should this be aware of scope/permissions/etc?
        
        dbConnection = self.getDatabaseConnection()
        sql = "SELECT distinct searchValue FROM latestAnswers where searchIndex = %s " % qstr(searchIndex)
        
        if modelIdentities:
            sql += " AND modelIdentity in (%s) " % qlist( modelIdentities )
            
       
        if limit:
            sql += " LIMIT %d" % int(limit)
       
        #print sql
       
        rs = self.sqlQuery( sql )
        
        results = []
        for row in rs:            
            results.append( row['searchValue'] )
            #yield row[0]
                        
            
        return results
            
    #---------------------------------------------------------------#            
            
    security.declarePublic('getSearchIndices')            
    def getSearchIndices(self, onlyPopulated=False):   
        """ Returns a list of all available search indices """
                         
        sql = """
               SELECT   searchIndex, valueType, count(distinct searchValue) as num 
               FROM     answers 
               WHERE    searchIndex is not NULL and trim(searchIndex) != ""
               GROUP BY searchIndex
              """
               
        if onlyPopulated:
            sql += "HAVING num > 0"               
        
        dbConnection = self.getDatabaseConnection()
        cols, rs = dbConnection.query( sql )
        
        return rs
                 
    #---------------------------------------------------------------#                 
                 
    security.declarePublic('getComponentAnswers')            
    def getComponentAnswers(self, componentIdentities, assessmentIdentities=None, search=None, repositoryIdentities=None):
        """
         For each assessment in the same model as each componentIdentity, 
         work out all the components and their values. Intended for use with KERF in the Link component
         
         Returns a key=>value mapping of componentIdentity => value and searchIndex => searchValue
        """
        
        if not componentIdentities: return
        
        cleaned = [qstr(cid) for cid in componentIdentities] # Quote all componentIdentities
        
        filtersql = ""
        if search:
            search = qstr(search, False)
            filtersql += " AND value LIKE '%%%s%%' or searchValue LIKE '%%%s%%' " % (search, search)
                
        if assessmentIdentities:
            pass
            qaids = []
            for aid in assessmentIdentities:
                qaids.append( qstr(aid) )
            #qaids = [qstr(aid) for aid in assessmentIdentities]
            filtersql += " AND assessmentIdentity IN (" + ','.join(qaids) + ")" 
        
        sql = """
            SELECT DISTINCT latestAnswers.assessmentIdentity, latestAnswers.modelIdentity, latestAnswers.componentIdentity, latestAnswers.value, 
                            searchIndex, searchValue
            FROM latestAnswers
            LEFT OUTER JOIN repositories ON (
                repositories.assessmentIdentity = latestAnswers.assessmentIdentity 
                and repositories.modelIdentity  = latestAnswers.modelIdentity
            )   
            WHERE componentIdentity in (""" + ','.join(cleaned) + """) 
            AND latestAnswers.assessmentIdentity IN
            (                            
                SELECT DISTINCT assessmentIdentity
                FROM latestAnswers 
                WHERE componentIdentity in (""" + ','.join(cleaned) + """) 
                %s
                ORDER BY assessmentIdentity DESC
            )
            
            """ % filtersql
                
                
                
        if repositoryIdentities:
            
            qrepositoryIdentities = [qstr( identity ) for identity in repositoryIdentities]
            sql += " AND repositories.repositoryIdentity in (%s) " % (','.join( qrepositoryIdentities ))
        
        
        
        
        dbConnection = self.getDatabaseConnection()
        cols, rs = dbConnection.query( sql )
        
        data = {}      # Stores the component => value and searchIndex => searchValue mappings. 
        lastAid = None # Last assessment identity
        
        aid = None     # Current assessmentIdentity
                       # (Declared here so it's available at the end of the loop to yield the
                       #  remaining data)
        
        """
            Build a dictionary of componentId, Value for each assessment.
            
            Each assessment consists of multiple rows from the resultset, sorted by assessmentIdentity
            We iterate over each row, building a dictionary of searchIndex => searchValue and 
            componentIdentity => value for each component.
            
            Once we hit the end of one assessment in the resultset, we yield the results, reset the 
            dictionary, and keep going for the next assessment.
        """
        
        # For each row in the resultset
        for row in rs:
            
            # Extract the data
            aid   = row[0]  
            cid   = row[2]  
            value = row[3]            
            searchIndex = row[4]
            searchValue = row[5]
            
            #print "AID: %s\t\tVALUE: %s" % (aid, value)
            
            # If we've hit the next assessment, yield the data from the previous assessment
            # and start a new brain.
            if lastAid is not None and aid != lastAid:
                #print "Yielding: %s" % `data`
                yield lastAid, data
                data = {}
                
            # Keep track of which assessment we are looking at.
            lastAid = aid
                
            # Store the componentIdentity => value mapping
            data[cid] = value
            
            # If set, store the searchIndex => searchValue mapping
            if searchIndex and searchValue:
                data[searchIndex] = searchValue
            

        # Yield the final assessment  
        if aid:
            yield aid, data
           
        
        
    #---------------------------------------------------------------#
    
    security.declarePublic('linkToAssessment')
    def linkToAssessment(self, searchIndex):
        """ 
            Returns a list of assessmentId => searchValue's for the specified searchIndex 
            Used by the Lookup component for custom queries. 
            (No longer needed with Link components and getComponentAnswers() ?)
         """
        
        sql = "select distinct assessmentIdentity, searchValue from latestAnswers where searchIndex=%s"
        
        dbConnection = self.getDatabaseConnection()
        cols, rs = dbConnection.query( sql % qstr(searchIndex))
        
        return rs # ( key, value )
    
    
    
    #---------------------------------------------------------------#

    security.declarePublic('createAssessment')        
    def createAssessment(self, model, userIdentity = None, repositoryIdentity = None, cloneAssessmentIdentity=None):
        """
            Creates and returns an new Assessment instance
            for the specified model and assessor.
            
            NOTE: Does not initially persist the assessment (Unless cloning).  
            To persist a new assessment you must call 'saveAssessment'
            on this interface
            
            RETURN TYPE: Assessment
        """
        
        generator_id = model.getAssessmentIdentityTemplate()
        
        if generator_id:
            generator = getattr(model, generator_id.strip())
            assessmentIdentity = qstr(generator( model ), quote=False)
        else:
            assessmentIdentity = generateIdentity("aid")

       
        # When cloning an assessment, we disregard the model argument and use the same model as the original.
        if cloneAssessmentIdentity:
            oldAssessment = self.loadAssessment(cloneAssessmentIdentity)
            newModel = oldAssessment.getModel()
            
            #raise "New model is " + `newModel`
       
        # Create our new Assessment object
        assessment = Assessment(assessmentIdentity, model, userIdentity, repositoryIdentity, revision=0)
        
        # If the "Clone" options is checked, we want to copy all assessment data from the existing assessment
        # into this one.
        if cloneAssessmentIdentity:
            
            # First, persist it in the database (Should only create an entry in the "Assessments" table
            self.saveAssessment( assessment )
            
            oldAssessmentIdentity = self.qstring( cloneAssessmentIdentity ) 
            newAssessmentIdentity = self.qstring( assessment.getIdentity() )
            revision              = assessment.getRevision()
            
            # Remove and previous data from excessive saveAssessment calls
            # first. 
            self.sqlQuery("""
                DELETE FROM answers WHERE assessmentIdentity=%s AND modelIdentity=%s
            """ % (qstr(assessment.getIdentity()), qstr(model.getIdentity())))

            # Next, we copy all the data from the previous component
            self.sqlQuery( """
                INSERT INTO answers (assessmentIdentity, revision, modelIdentity, componentIdentity, instantCreated, instantModified, modifiedByUser,
                                     value, searchIndex, searchValue)
                                     
                (SELECT %s, %d, modelIdentity, componentIdentity, instantCreated, instantModified, modifiedByUser, value, searchIndex, searchValue
                 FROM latestAnswers WHERE assessmentIdentity = %s)
                """ % ( newAssessmentIdentity, revision, oldAssessmentIdentity ))
                
            # Assessment data copied.                 
            
        # Return the finished Assessment object            
        return assessment
            
    #---------------------------------------------------------------#
        
    security.declarePublic('saveAssessment')        
    def saveAssessment(self, assessment):
        """
            Saves the specified assessment and updates
            it's current in-memory state (so that it isn't new or modified)
        """

        # We don't need to proceed if the assessment isn't new or hasn't been
        # changed
        if not assessment.getIsNew() and not assessment.getIsModified():
            print "Nothing to save for assessment %s revision %s" % ( assessment.getIdentity(), `assessment.getRevision()` )
            return
        print "Saving assessment %s revision %s" % ( assessment.getIdentity(), `assessment.getRevision()` )


        # Is the assessment modified? (if so let's update the modified time)
        if assessment.getIsModified():
            assessment.setInstantModified(DateTime())
        
        #get the sql creation and modified time instants values
        instantCreated  = assessment.getInstantCreated().strftime(DATETIME_FORMAT)
        instantModified = assessment.getInstantModified().strftime(DATETIME_FORMAT)
        
        assessmentRevision = assessment.getRevision()
        
        #determine assessment credit transaction identity
        if assessment.getCreditTransactionIdentity() is None:
            creditTransactionIdentity = 'null'
        else:
            creditTransactionIdentity = "'%s'" % assessment.getCreditTransactionIdentity()

        #create or update the assessment
        if assessment.getIsNew():
            #create a new assessment
            sql = "INSERT INTO assessments (assessmentIdentity, modelIdentity, instantCreated, instantModified, createdBy, isLocked, name, checkedOutBy, creditTransactionIdentity) VALUES ('%s', '%s', '%s', '%s', '%s', %d, '%s', %s, %s)" % (assessment.getIdentity(), assessment.getModelIdentity(), instantCreated, instantModified, assessment.getCreatedBy(), assessment.getIsLocked(), toSQL(assessment.getName()), qstr(assessment.getCheckedOutBy()), creditTransactionIdentity) 
            self.sqlQuery(sql)

        # Save all of the component state in the assessment (only if the
        # assessment is modified)
        componentStates = assessment.getComponentStates()
        assessmentOnlyModified = assessment.getIsModified() and not assessment.hasModifiedComponentStates()

        newRevision = assessment.createNewRevision()

        #print "New Revision is %s" % `newRevision`

        modifiedComponents = []
        for componentState in componentStates:
            # If the assessment is new or the specific companent state has
            # been modified, insert a new row. Otherwise just update the
            # revision of the component state
            if assessment.getIsNew() or componentState.getIsModified():
                # Ensure the component state's revision is updated when saving 
                componentState.setRevision(newRevision)

                # Save the current component state using this db connection
                self.saveComponentState(componentState, assessment.getInstantModified(), assessment.getCheckedOutBy())

                # It's highly likely that there will be more components that
                # haven't been modified, so let's keep track of only those
                # modified
                modifiedComponents.append(componentState.getComponentIdentity())

        
        # If component states have been modified, update all the others to
        # reflect the new revision and ensure the assessments revision is also
        # updated
        # 
        # if len(modifiedComponents) or (assessment.getIsModified() and not assessment.hasModifiedComponentStates()):
        # 
        if len(modifiedComponents) or assessmentOnlyModified:
            
            # jamesd 2007-06-21 Trac #923
            # Changed the order if the following two SQL Queries.
            #
            # Originally, we were updating answers table before the assessments table. 
            # This wasn't a problem, unless we had an "Assessment title template" 
            # The problem:
            # 
            #    * We save the component with the new revision to the database normally.
            #    
            #    * Next, when saving assessment metadata, we call assessment.getName()
            #    
            #    * If the title refers to a component, getName() resolves this and returns it's value
            #    
            #    * However, the component in question had just been updated with a new revision, 
            #      temporarily putting the assessments and answers table out of sync
            #
            #    * As a result, the latestAnswers table doesn't see any values for that component
            #      for that assessment
            #
            #    * AssessmentStorageService assumed no value exists for the component, loads the default blank entry, 
            #      And caches it. This sets the name to nothing, and any further requests for that components value within     
            #      the same request return null.
            #
            # Changing the order of these queries ensures the SQL Database is in a sane state during the whole transaction
            # and the component value is readable during the entire process
            #    
            
            q = qstr
            
            # Update the assessment only it has been modified
            sql = """
                UPDATE assessments 
                SET 
                    instantModified=%s, 
                    createdBy=%s, 
                    responsible=%s,
                    isLocked=%d, 
                    name=%s, 
                    latestRevision=%d, 
                    checkedOutBy=%s, 
                    creditTransactionIdentity=%s 
                WHERE 
                    assessmentIdentity=%s 
                AND modelIdentity=%s
                """ % (q(instantModified), q(assessment.getCreatedBy()), q(assessment.getResponsible()), assessment.getIsLocked(), q(assessment.getName()), 
                       newRevision, q(assessment.getCheckedOutBy()), q(creditTransactionIdentity), q(assessment.getIdentity()), q(assessment.getModelIdentity()))
                
            self.sqlQuery(sql)
            
            componentIdentities = "','".join(modifiedComponents)
            sql = """
                UPDATE
                    answers
                SET
                    revision=%d
                WHERE
                    assessmentIdentity='%s'
                    AND modelIdentity='%s'
                    AND revision=%d
                    AND componentIdentity NOT IN ('%s')
                """ % (newRevision, assessment.getIdentity(), assessment.getModelIdentity(), assessmentRevision, componentIdentities)
            self.sqlQuery(sql)



            # TODO: we could check here if a concurrent update has occured and
            # bail out... but we won't.  we'd probably throw an exception if
            # we found a problem here


        #save the attributes
        for attribute in assessment.getAttributes():
            #save the current attribute using this db connection
            self.saveAttribute(attribute, assessment.getInstantModified())


        #the assessment is no longer new or modified
        assessment.setIsNew(false)
        assessment.setIsModified(false)        

        
    #---------------------------------------------------------------#
    
    def _saveSearchData( self, assessmentIdentity, assessmentData ):
        """ Overwrites the `search` table for the specified assessment """
        
        schema = self.describeSearchTable()
        
        sql = "REPLACE search SET "        
        
        
        assessment = self.loadAssessment( assessmentIdentity ) 
        model = assessment.getModel()
        
        # Start off the clauses with basic metadata
        clauses = [ "assessmentIdentity = %s" % qstr( assessment.getIdentity()),
                    "modelIdentity      = %s" % qstr( model.getIdentity() ),
                    "searchRelation     = %s" % qstr( model.getSearchRelation() ) ]
        
        # Generate the clauses to save the searchIndex data
        for key, value in assessmentData.items():            
            if key in schema:
                clauses.append( "`%s` = %s" % (self.qstring( key, quote=False ), self.qstring( value )) )
            
        # Combine the clauses into SQL            
        sql += " , ".join( clauses )
                
        # Save the assessment data 
        self.sqlQuery( sql )
        
        # Update the assessment name if needed
        sql = "UPDATE assessments SET name = '%s' WHERE assessmentIdentity = '%s' AND modelIdentity = '%s'" % \
                    (toSQL(assessment.getName()), assessment.getIdentity(), assessment.getModelIdentity())
                    
        self.sqlQuery( sql )
        
    
    #---------------------------------------------------------------#
    
    def reindexAssessments(self, modelIdentity=None):
        """ Reindex searchindex data """
        
        out = StringIO()
        
        
        if modelIdentity: conditions = " AND modelIdentity = %s " % qstr( modelIdentity )
        else: conditions = ""    
        
        sql = """
            SELECT assessmentIdentity, searchIndex, searchValue 
            FROM latestAnswers 
            WHERE searchIndex is NOT NULL and searchIndex != ""
            %s
            ORDER BY assessmentIdentity
        """ % conditions
            
        rs = self.sqlQuery( sql )
                
        thisAssessmentIdentity = None
        thisAssessmentData = {}
        
        # Collate and save each assessments data
        for row in rs:
            
            assessmentIdentity = row['assessmentIdentity']
            searchIndex        = row['searchIndex']
            searchValue        = row['searchValue']
            
            # If we've hit the end of one assessment in the resultset, save it .
            if assessmentIdentity != thisAssessmentIdentity:

                print >> out, "Reindexing %s" % `thisAssessmentIdentity`
                
                # Save the data
                if thisAssessmentData:                             
                    self._saveSearchData( thisAssessmentIdentity, thisAssessmentData )
                    
                # Keep track of which assessment we are processing now
                thisAssessmentIdentity = assessmentIdentity
                thisAssessmentData     = {}
                    
            # Save assessment data for this row                    
            thisAssessmentData[ searchIndex ] = searchValue
            
                    
        # Last item in list needs to be saved
        if thisAssessmentData:
            self._saveSearchData( thisAssessmentIdentity, thisAssessmentData )
            
            
        print >> out, "Done"
        return out.getvalue()            
                
    #---------------------------------------------------------------#
      
    def getActiveSearchIndexesForModel( self, modelIdentity ):
        """ Returns a list of search indexes in use for the specified model """
              
        rs = self.sqlQuery( "SELECT DISTINCT componentIdentity, searchIndex FROM latestAnswers WHERE modelIdentity = %s" % qstr(modelIdentity) )
        
        results = {}
        
        for row in rs:
           results[row['componentIdentity']] = row['searchIndex']
           
        return results
      
    #---------------------------------------------------------------#      
      
    def reindexAssessmentsDeep(self, modelIdentity=None, componentIdentities=None):
        """ 
            Reindex all assessments, (Optionally for a specific model), taking into account
            new search indexes that have been added since assessments completed.     
        """
                
        dbConnection = self.getDatabaseConnection()
            
        out = StringIO()
        
        def dbg(x):
            print x
            print >> out, x
                    
            
        if modelIdentity:
            models = [self.getComponentByIdentity( modelIdentity )]
        else:
            models = self.getModels() 
            
        print "Reindexing models: %s" % `[model.title_or_id() for model in models]`
            
        for model in models:
            
            print "Model: %s" % model.title_or_id()
            
            user = self.REQUEST.AUTHENTICATED_USER
            assessments = self.getAssessmentsSatisfyingFilter( model.getAllAssessmentsFilter(), user )
            
            if componentIdentities:            
                myComponentIdentities = componentIdentities
            else:
                # Get all indexes that are defined in the model, or are currently in use in the database.
                # This should take into account both Indexes that have been added, and indexes that have been removed
                # since the last reindex
                indexes = model.getSearchIndexSummary()
                indexes.update( self.getActiveSearchIndexesForModel( model.getIdentity() ) )
                
                print "Indexes: %s" % indexes
                
                myComponentIdentities = indexes.keys()
                        
            #components  = model.getDescendantsByInstanceOf("Assessible")
            
            num_assessments = len(assessments)
            count = 0
                       
            for assessment in assessments:
                
                count += 1
                
                print "Assessment %d of %d - %s" % ( count, num_assessments, assessment.getIdentity() )
                
                sql = "UPDATE assessments SET name = '%s' WHERE assessmentIdentity = '%s' AND modelIdentity = '%s'" % \
                    (toSQL(assessment.getName()), assessment.getIdentity(), assessment.getModelIdentity())
                    
                self.execute( sql )                    
                
                for componentIdentity in myComponentIdentities: 
                    
                    component = self.getComponentByIdentity( componentIdentity )
                    
                    if not component:
                        continue
                        print "Component not found: %s" % componentIdentity
                        #raise ComponentNotFound, componentIdentity
                    
                    if not component.isShown(assessment): continue # Don't reindex components hidden by showIf
                    
                    componentState = assessment.getComponentState( componentIdentity )
                    try:
                        componentValue = componentState.getValue()
                        component.updateAssessmentState( assessment, componentValue )
                        self.saveComponentState( componentState )
                    except ValidationException, e:                        
                        dbg ("reindexAssessments: Validation error on %s - %s" % (component.getIdentity(), str(e)))
                    
                    except Exception, e:
                        dbg ("Error reindexing component '%s' - %s" % (component.getIdentity(), str(e))) 
                        raise
                    
                                    
                    
        dbg("Done")
        return out.getvalue()

    #---------------------------------------------------------------#

    def getMultiValuedIndexes(self):
        """ 
            Returns a list of all indexes that have more than one value per assessment, 
            thus won't be indexed correctly in the search table
            
            This is used for search scripts to know when to Join against the main answers table in SQL Queries
        """
        
        sql = '''
            select searchIndex, count(searchIndex) as numInstances
            from latestAnswers
            where searchIndex is not null and searchIndex != ""
            group by assessmentIdentity, searchIndex
            having numInstances > 1
        '''
        
        rs = self.sqlQuery( sql )
        return [row['searchIndex'] for row in rs]
            
                        
                        
                        
    #---------------------------------------------------------------#                    

    security.declarePublic('assessmentDataExists')
    def assessmentDataExists(self, assessmentIdentity):
        """
            Returns a True if assessment data exists for the given assessment identity
            
            RETURN TYPE: Boolean
        """

        resultSet = self.execute("SELECT assessmentIdentity FROM latestAnswers WHERE assessmentIdentity = %s" % qstr(assessmentIdentity));
        
        if resultSet:
            return True
        
        return False

    #---------------------------------------------------------------#                    

    def getModelsForAssessment( self, assessmentIdentity ):
        """ Returns a list of models for the specified assessment """
        
        if not hasattr(self, '_v_cache_getModelsForAssessment'):
            self._v_cache_getModelsForAssessment = {}
        else:
            if assessmentIdentity in self._v_cache_getModelsForAssessment:
                return self._v_cache_getModelsForAssessment[ assessmentIdentity ]
        
        rs = self.execute("SELECT modelIdentity FROM assessments WHERE assessmentIdentity = %s" % qstr(assessmentIdentity))

        results = []
        
        # Iterate over the resultset, get each model, and add it to the list.
        for row in rs:
            model = self.getComponentByIdentity( row[0] )
            if model: results.append( model )
            
        self._v_cache_getModelsForAssessment[ assessmentIdentity ] = results            
            
        return results
            
        
        

    #---------------------------------------------------------------#                    

    security.declarePublic('loadAssessment')        
    def loadAssessment(self, assessmentIdentity, models=None, userIdentity = None, repositoryIdentity = None, revision=None):
        """
            Loads the assessment with the specified
            assessment and model from storage.
            
            NOTE 1: Returns None if the assessment is unknown
            
            NOTE 2: Does not necessarily load all of the component
            state for all Assessible components in the model.
            
            RETURN TYPE: Assessment
        """        

        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()
        
        if not revision:
            revision = self.getLatestAssessmentRevision(assessmentIdentity)

        if not models:
            models = self.getModelsForAssessment( assessmentIdentity )
            if not models:
                raise "Could not find models for %s" % assessmentIdentity

        if type(models) not in (list, tuple):
            models = [models]

        modelHash = {}
        for model in models:
            modelHash[ model.getIdentity() ] = model
            
        identities = [qstr(identity) for identity in modelHash.keys()]

        #determine if the assessment exists
        sql = "SELECT * FROM assessments WHERE assessmentIdentity = '%s' AND modelIdentity in (%s)" % (assessmentIdentity, ','.join(identities))

        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))
        #(columns, resultset) = dbConnection.query(sql)
        resultset = self.sqlQuery( sql )
        
        #did we find the assessment?
        if len(resultset) > 0:
            #get the assessment row
            values = resultset[0]
            
            model = modelHash[ values['modelIdentity'] ]
        
            #instantiate an Assessment and return it using the persistent values
            assessment = Assessment(assessmentIdentity, model, userIdentity, repositoryIdentity, revision=values['latestRevision'])

            assessment.setInstantCreated(values['instantCreated'])               # instantCreated
            assessment.setInstantModified(values['instantModified'])              # instantModified
            assessment.setCreatedBy(values['createdBy'])                    # createdBy
            assessment.setResponsible(values['responsible'])                    # createdBy
            # values[5] is the assessment revision                # latestRevision
            assessment.setIsLocked(values['isLocked'] == 1)                # isLocked
            assessment.setName(values['name'])                         # name
            assessment.setCheckedOutBy(values['checkedOutBy'])                 # checkedOutBy
            assessment.setCreditTransactionIdentity(values['creditTransactionIdentity'])    # creditTransactionIdentity

            assessment.setIsNew(False)
            assessment.setIsModified(False)

            return assessment 

        #not found, so return nothing
        return None
        
    #---------------------------------------------------------------#
        
    security.declarePublic('checkoutAssessment')        
    def checkoutAssessment(self, assessment, user):
        """
            Attempts to checkout the specified assessment
            for the specified model and user
            
            If successful, the checkedOutBy property of the returned
            assessment will equal user.getIdentity()
            
            Otherwise either the assessment is in use by another user
            or the model supports concurrent assessment.
            
            RETURN TYPE: Assessment
        """
        
        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()

        #does the model support concurrent assessment?
        if assessment.getModel().getPermitsConcurrentAssessment():
            assessment.setCheckedOutBy(None)
            
        else:
            #is the assessment new? (if so we don't need to check it out... we just set the checkout user)
            if assessment.getIsNew():
                assessment.setCheckedOutBy(user.getIdentity())
            else:
                #attempt to lock the assessment in the db (so we can set the user that is checking out the assessment) 
                #note: it appears as if zope does the locking for us... so there is no reason to use 'FOR UPDATE'
                sql = "SELECT checkedOutBy, instantModified FROM assessments WHERE assessmentIdentity = '%s' AND modelIdentity = '%s'" % (assessment.getIdentity(), assessment.getModel().getIdentity())
        
                #log the sql so we can see what is going on
                log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

                (columns, resultset) = dbConnection.query(sql)
                
                #did we find the assessment?
                if len(resultset) > 0:
                    #get the assessment row
                    values = resultset[0]

                    #who has the assessment checked out?
                    checkedOutBy = values[0]
                    lastModified = values[1]

                    #is the assessment checkedout by anyone or is it already checkedout by the specified user
                    if checkedOutBy is None or checkedOutBy == "" or checkedOutBy == user.getIdentity():
                        #we're going to check it out by the specified user
                        checkedOutBy = user.getIdentity()
                        assessment.setCheckedOutBy(user.getIdentity())                        
                        
                    else:
                        #it's already checked out by someone else...
                        #has the time since the other person last updated the assessment longer than the maximum checkout time?
                        if lastModified.lessThan(DateTime().timeTime() - (getMaximumAssessmentCheckoutTime() * 60)):
                            #the previous user's checkout time has expired...

                            #we're going to check it out by the specified user
                            checkedOutBy = user.getIdentity()
                            assessment.setCheckedOutBy(user.getIdentity())                        

                    #update the checked out status for the assessment
                    sql = "UPDATE assessments SET checkedOutBy = '%s' WHERE assessmentIdentity = '%s' AND modelIdentity = '%s'" % (checkedOutBy, assessment.getIdentity(), assessment.getModel().getIdentity())
                    dbConnection.query(sql)
                    
                else:
                    #not found, so return the assessment without changing the checkedOutBy property
                    pass
                    
        return assessment            
        
    #---------------------------------------------------------------#
        
    security.declarePublic('checkinAssessment')        
    def checkinAssessment(self, assessment, user):
        """
            Attempts to checkin the specified assessment.
            
            If successful, the checkedOutBy property of the returned
            assessment will be None.  
            
            Otherwise the assessment is in use by another user or
            it supports concurrent assessment
            
            RETURN TYPE: Assessment
        """
            
        pass            
        
        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()

        #does the model support concurrent assessment?
        if assessment.getModel().getPermitsConcurrentAssessment():
            assessment.setCheckedOutBy(None)
            
        else:
            #is the assessment new? (if so we don't need to check it in...)
            if assessment.getIsNew():
                #nothing to do as not persistent yet
                pass
                
            else:
                #attempt to lock the assessment in the db (so we can set the user that is checking in the assessment) 
                #note: it appears as if zope does the locking for us... so there is no reason to use 'FOR UPDATE'
                sql = "SELECT checkedOutBy FROM assessments WHERE assessmentIdentity = '%s' AND modelIdentity = '%s'" % (assessment.getIdentity(), assessment.getModel().getIdentity())

                #log the sql so we can see what is going on
                log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

                (columns, resultset) = dbConnection.query(sql)
                
                #did we find the assessment?
                if len(resultset) > 0:
                    #get the assessment row
                    values = resultset[0]

                    #who has the assessment checked out?
                    checkedOutBy = values[0]

                    #is the assessment checkedout by anyone or is it already checkedout by the specified user
                    if checkedOutBy is None or checkedOutBy == "" or checkedOutBy == user.getIdentity():
                        #we're going to check it out by the specified user
                        checkedOutBy = 'null'
                        assessment.setCheckedOutBy(None)                        
                        
                    else:
                        #it's already checked out by someone else...
                        checkedOutBy = "'%s'" % checkedOutBy

                    #update the checked out status for the assessment
                    sql = "UPDATE assessments SET checkedOutBy = %s WHERE assessmentIdentity = '%s' AND modelIdentity = '%s'" % (checkedOutBy, assessment.getIdentity(), assessment.getModel().getIdentity())
                    dbConnection.query(sql)
                    
                else:
                    #not found, so return the assessment without changing the checkedOutBy property
                    pass
                    
        return assessment            
        
    #---------------------------------------------------------------#
        
    security.declarePublic('deleteAssessmentWith')        
    def deleteAssessmentWith(self, assessmentIdentity, modelIdentity):
        """
            Deletes the specified assessment from storage
            
            (including within repositories)
        """
        
        # Queries to remove the assessment from various places
        sqlQueries = [
            "DELETE FROM assessments WHERE assessmentIdentity = '%s' and modelIdentity = '%s'" % (assessmentIdentity, modelIdentity),
            "DELETE FROM answers WHERE assessmentIdentity = '%s' and modelIdentity = '%s'" % (assessmentIdentity, modelIdentity),
            "DELETE FROM repositories WHERE assessmentIdentity = '%s' and modelIdentity = '%s'" % (assessmentIdentity, modelIdentity),
            "DELETE FROM search WHERE assessmentIdentity = %s and modelIdentity = %s" % (qstr(assessmentIdentity), qstr(modelIdentity))
        ]

        for query in sqlQueries:
            self.sqlQuery(query)
        
    #---------------------------------------------------------------#

    security.declarePublic('getAssessmentSearchIndexValue')
    def getAssessmentSearchIndexValue(self, assessment, searchIndex, default=None, isMulti=False, ignoreComponents=[]):
        """
            Returns the value of the specified searchIndex on the given assessment
        """
        
        searchIndex = qstr(searchIndex)
        assessmentIdentity = qstr(assessment.getIdentity())
        
        #sql = "SELECT `%s` from search where assessmentIdentity = %s" % (searchIndex, assessmentIdentity)
        sql = """
            SELECT DISTINCT searchValue 
            FROM  latestAnswers 
            WHERE searchIndex = %s 
            AND   assessmentIdentity = %s
            AND   searchValue != ""
            AND   searchValue is not null
            """ % ( searchIndex, assessmentIdentity)
        
        if ignoreComponents:
            sql += " AND componentIdentity NOT IN (%s) " % qlist( ignoreComponents )
        
        rs = self.execute( sql )
        
        results = []
        for row in rs:
            if isMulti: 
                results.append( row[0] )
            else:
                return row[0]
    
        if results:
            return results
        else:
            return default

    #---------------------------------------------------------------#

    security.declarePublic('loadComponentState')
    def loadComponentState(self, assessment, componentIdentity, dbConnection = None):
        """
            ABSTRACT: Loads the component state for the specified
            component and returns the AssessmentComponentState.
            
            NOTE 1: Does not modify the assessment
            
            NOTE 2: Returns None if the component state doesn't exist
            or could not be loaded
        
            RETURN TYPE: AssessmentComponentState
        """
        
        #attempt to get the component from the component identity
        #(we do this so we can determine which table to look in)
        component = assessment.getModel().getComponent(componentIdentity)
        
        if not component:
            raise ComponentNotFound, "Could not find component %s" % `componentIdentity`
        
        
        #did we find the component?
        if component:
            searchIndex = component.getSearchIndex()
            valueType = component.getAssessmentValueType()

            # Sort out the revision number
            revision = assessment.getRevision()
            if not revision:                
                revision = self.getLatestAssessmentRevision( assessment.getIdentity() )

            # Grab the component state
            sql = """
                    SELECT
                        assessmentIdentity, modelIdentity,
                        componentIdentity, instantCreated,
                        instantModified, value,
                        searchValue, modifiedByUser
                    FROM latestAnswers
                    WHERE assessmentIdentity='%s'
                        AND modelIdentity='%s'
                        AND componentIdentity='%s'
                    ORDER BY revision DESC
                  """ % (assessment.getIdentity(), 
                         assessment.getModelIdentity(), 
                         componentIdentity)
            resultset = self.execute( sql )
            
            #-- AND revision = %d  -- Jamesd 20070621 - No need to filter on revision if we're querying latestRevisions ?
            
            
           
            
            # Make sure we've got some results before proceeding
            if not len(resultset):
                return None

            # Iterate over the resultset, properly casting and assigning the values and
            # various metadata
            value = []
            searchValue = []
            instantCreated = instantModified = modifiedByUser = None
            for row in resultset:
                if row[5]:
                    # Cast the values to their proper types
                    if valueType == st_BOOLEAN:
                        value += [iif(row[5]=="1", true, iif(row[5]=="0", false, undefined))]
                    elif valueType == st_FLOAT:
                        value += [float(row[5])]
                    elif valueType == st_INTEGER:
                        value += [int(row[5])]
                    else: # Defaults to st_STRING
                        value += [row[5]]
                
                if row[6]:
                    searchValue += [row[6]]

                instantCreated = row[3]
                instantModified = row[4]
                modifiedByUser = row[7]
            
            # Make sure we remove the list if the component is not multivalue aware
            if not component.isMultiValued():
                if len(value) > 0:
                    value = value[0]
                else:
                    value = ''
                
                if len(searchValue) > 0:
                    searchValue = searchValue[0]
                else:
                    searchValue = None

            # 20070102 johnm    Changing the way the AssessmentComponentState is
            #                   created ... we're now going to have the component
            #                   itself return it's version of state. This gives us
            #                   additional flexibility in templates etc.
            assessmentComponentState = component.getComponentState(
                assessment.getIdentity(),
                valueType,
                value,
                revision=revision,
                searchIndex=searchIndex,
                searchValue=searchValue
            )

            #the component state is now stored
            assessmentComponentState.setInstantCreated(instantCreated)
            assessmentComponentState.setInstantModified(instantModified)
            assessmentComponentState.setModifiedByUser(modifiedByUser)

            assessmentComponentState.setIsNew(false)
            assessmentComponentState.setIsModified(false)

            return assessmentComponentState                

        return None
        
    #---------------------------------------------------------------#


    security.declarePublic('existsComponentState')
    def existsComponentState(self, assessment, componentIdentity, dbConnection = None):
        """
            Returns if persistent state exists for the
            specified component for the provided assessment.
            
            NOTE: This method checks the underlying persistent storage
            for the specified component's state.  It does not check 
            the 'cached' assessment for the component state.  That is,
            this method will return 'false' if the underlying 
            persistent storage does not contain the component state
            even if the provided assessment contains the component
            state.
            
            RETURN TYPE: Boolean
        """            

        #attempt to get the component from the component identity
        #(we do this so we can determine which table to look in)
        component = assessment.getModel().getComponent(componentIdentity)
        
        #did we find the component?
        if component is not None:
            #get the type of the component assessment state
            valueType = component.getAssessmentValueType()
            
        
            #sql to find the component state
            sql = "SELECT * FROM latestAnswers WHERE assessmentIdentity = '%s' and modelIdentity = '%s' and componentIdentity = '%s'" % (table, assessment.getIdentity(), assessment.getModelIdentity(), componentIdentity)
        
            #get a database connection to work with (if we don't have one)
            if dbConnection is None:
                dbConnection = self.getDatabaseConnection()

            #log the sql so we can see what is going on
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

            #did we find any state?
            (columns, resultset) = dbConnection.query(sql)
            return len(resultset) > 0
        
        else:
            #we assume that if the component is unknown, there is has no state!
            return false
        
    #---------------------------------------------------------------#

    security.declarePrivate('deleteAllComponentState')
    def deleteAllComponentState(self, component):
        """
           Deletes all of the component state for the
            specified component.
            
            This method is called when a component is deleted to
            clean up the assessment state for the said component
        """
        
        #get the type of the component assessment state
        valueType = component.getAssessmentValueType()

        #sql to find the component state
        sql = "DELETE FROM answers WHERE modelIdentity = '%s' and componentIdentity = '%s'" % (table, component.getModel().getIdentity(), component.getIdentity())

        #get a database connection to work with (if we don't have one)
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #delete the component state
        dbConnection.query(sql)
            
    #---------------------------------------------------------------#

    security.declarePublic('describeSearchTable')
    def describeSearchTable(self, nocache=False, result=[]):
        """
            Returns a list of fields available in the search table
        """
        
        # Python treats result as a static variable, so we use it for caching
        # If we've cached the result, return it.
        #if result and not nocache: return result 
        
        result = [] # FIXME - Ditch the cache for now, causing too many issues. Reimplement with memcached?
         
        rs = self.execute("DESCRIBE search")
        
        #print "Describe: %s" % `list(rs)`
                
        for row in rs:
            
            name = row[0]
            if name not in ('id','assessmentIdentity','modelIdentity','searchRelation'):
                result.append( name )
        
        return result

    #---------------------------------------------------------------#

    security.declarePublic('saveComponentState')
    def saveComponentState(self, assessmentComponentState, instantModified = None, userIdentity = None, dbConnection = None):
        """
            Stores the specified assessment component state
            
            instantModified (optional) a DateTime instance that 
            is used to force the modification time of the component state
            to a specified value
            
            (generally called by self.saveAssessment)
        """
        
        # --------------------------------------------------------------------
        # If we're just updating the revision of the component, we want to
        # keep it's modification time static.
        # ... otherwise create one
        if instantModified is None:
            instantModified = DateTime()
        
        # Set the new modification time if this component isn't new
        if not assessmentComponentState.getIsNew() and assessmentComponentState.getIsModified():
            assessmentComponentState.setInstantModified(instantModified)

        # Get the sql creation and modified time instants 
        instantCreated  = assessmentComponentState.getInstantCreated().strftime(DATETIME_FORMAT)
        instantModified = assessmentComponentState.getInstantModified().strftime(DATETIME_FORMAT)


        # --------------------------------------------------------------------
        # Get the type of the component assessment state
        valueType = assessmentComponentState.getValueType()

        # Determine the query based on the component value type
        if valueType == st_BOOLEAN:
            #determine the persistent value
            value = iif(assessmentComponentState.getValue() == true, 1, iif(assessmentComponentState.getValue() == false, 0, -1)) 
        else:
            value = assessmentComponentState.getValue()
        
        # Cheat: If the value is not a list, make it one
        if type(value) is not types.ListType:
            values = [value,]
        else:
            values = value

        # HACK: Keep a record of thge empty component state
        if not values:
            values = ['',]


        # -------------------------------------------------------------------
        # 20061019 johnm    Remove old state for the revision
        removeQuery = """
            DELETE FROM
                answers
            WHERE
                assessmentIdentity  = %s AND
                modelIdentity       = %s AND
                revision            = %d AND
                componentIdentity   = %s
        """ % ( 
            qstr(assessmentComponentState.getAssessmentIdentity()),
            qstr(assessmentComponentState.getModelIdentity()),
            assessmentComponentState.getRevision(),
            qstr(assessmentComponentState.getComponentIdentity())
        )
        
        # Only delete the old entries if there are new values to populate with
        # (Issue #803 - affecting Toll site. when reindexing, all old revisions are deleted but new ones arn't added)
        # The `values = ['',]` line above will ensure 'empty' sets of data still trigger this delete.
        # - jamesd
        if values:
            self.sqlQuery(removeQuery)

        searchIndex = assessmentComponentState.getSearchIndex()
        searchValue = assessmentComponentState.getSearchValue()
        if type(searchValue) is not types.ListType:
            searchValues = [searchValue,]
        else:
            searchValues = searchValue


        for value in values:            
            index = values.index(value)
            
            
            ### This block replaced by __str__ in Entity.py
            ## Decode entity chooser values.
            #if hasattr(value, 'getIdentity'):
                #value = value.getIdentity()
            
            # HACK
            try:
                #print "Getting SI Value for: %s" % `searchValues[index]`
                searchValue = searchValues[index]
                if searchValue is not None:
                    searchValue = str( searchValue )
                    
            except IndexError:
                searchValue = None
            
            
            # jamesd 2006-11-23 - 'if not value' is preventing checkboxes from saving correctly. Needed?
            # jamesd 2006-12-13 Turns out it is, for PythonScript components returning null (Among potentially others).
            #                   Modifying to check if 'Is Null' rather than 'if evaluates to True'.
            #
            # (Leave this comment in, it's the kind of gotcha I'll forget in 6 months)
            
            if value is None:
                 value = ''
                 
            if searchValue is None:
                searchValue = ''

            # Marshall everything into a string (In particular, Entity objects)
            value = str(value)

            #brain = {
                #'assessmentIdentity': qstr(assessmentComponentState.getAssessmentIdentity()),
                #'modelIdentity'     : qstr(assessmentComponentState.getModelIdentity()),
                #'revision'          : assessmentComponentState.getRevision(),
                #'componentIdentity' : qstr(assessmentComponentState.getComponentIdentity()),
                #'instantCreated'    : qstr(instantCreated),
                #'instantModified'   : qstr(instantModified),
                #'modifiedByUser'    : qstr(userIdentity),
                #'value'             : qstr(value),
                #'valueType'         : qstr(valueType),
                #'searchIndex'       : qstr(searchIndex),
                #'searchValue'       : qstr(searchValue),
            #}

            
            

            sql =  "INSERT INTO answers SET " \
                  +"assessmentIdentity  = %s, " % qstr(assessmentComponentState.getAssessmentIdentity()) \
                  +"modelIdentity       = %s, " % qstr(assessmentComponentState.getModelIdentity()) \
                  +"revision            = %s, " % assessmentComponentState.getRevision() \
                  +"componentIdentity   = %s, " % qstr(assessmentComponentState.getComponentIdentity())\
                  +"instantCreated      = %s, " % qstr(instantCreated)\
                  +"instantModified     = %s, " % qstr(instantModified)\
                  +"modifiedByUser      = %s, " % qstr(userIdentity)\
                  +"value               = %s, " % qstr(value)\
                  +"valueType           = %s, " % qstr(valueType)\
                  +"searchIndex         = %s, " % qstr(searchIndex)\
                  +"searchValue         = %s  " % qstr(searchValue)\
            
            
            if assessmentComponentState.getComponentIdentity().endswith("121") and assessmentComponentState.getAssessmentIdentity() == "Brands-14":
                print sql

            self.sqlQuery(sql)
            
 
            # ----------------------------------------------------------------
            # Is there a searchIndex? If so, add to the search table.
            searchIndex = assessmentComponentState.getSearchIndex()
            indexableFields = self.describeSearchTable()
            
            #print "Indexable: %s" % `indexableFields`
            #print "This: %s"  % `searchIndex`
            
            if searchIndex and searchIndex in indexableFields:
                
                # FIXME - This is a very slow way of indexing the data. 
                # Perhaps we should collate data into a dictionary and save with one REPLACE statement at the end of the transaction instead? 
                
                model = self.getComponentByIdentity( assessmentComponentState.getModelIdentity() )
            
                #brain['searchRelation'] = qstr(model.getSearchRelation())
                #brain['uqsearchIndex']  = searchIndex
            
                sisql = """
                    INSERT INTO search (assessmentIdentity,     modelIdentity,     searchRelation,     %(uqsearchIndex)s)
                    VALUES             (%(assessmentIdentity)s, %(modelIdentity)s, %(searchRelation)s, %(searchValue)s)                    
                    ON DUPLICATE KEY UPDATE %(uqsearchIndex)s = %(searchValue)s
                    """ % dict( uqsearchIndex      = qstr( searchIndex, quote="`"),  
                                assessmentIdentity = qstr( assessmentComponentState.getAssessmentIdentity()),
                                modelIdentity      = qstr( assessmentComponentState.getModelIdentity()),
                                searchRelation     = qstr( model.getSearchRelation() ),
                                searchValue        = qstr( searchValue ))
                                

                print "Updating search index for: %s \t= %s" % (searchIndex, searchValue)
                print sisql

                self.sqlQuery(sisql)


        #the component state is now stored
        assessmentComponentState.setIsNew(false)
        assessmentComponentState.setIsModified(false)
        
    #---------------------------------------------------------------#

    security.declarePublic('loadAttribute')
    def loadAttribute(self, assessment, attributeHandle, dbConnection = None):
        """
            Loads the attribute for the specified
            component and returns the result as an 
            AssessmentAttribute representation.
            
            NOTE 1: Does not modify the assessment
            
            NOTE 2: Returns None if the attribute doesn't exist
            or could not be loaded
        
            RETURN TYPE: AssessmentComponentState
        """

        #determine the type of the attribute
        valueType = attributeHandle.getValueType()
            
        #let's assume we don't have to do any db work (we are very lazy now.  we only hit the db if we have too)
        sql = None

        #determine the query based on the attribute value type
        sql = """
            SELECT assessmentIdentity, modelIdentity, componentIdentity, instantCreated, instantModified, value 
            FROM answers a1
            WHERE assessmentIdentity='%s' 
            AND modelIdentity='%s' 
            AND componentIdentity='%s'
            and revision = (
                SELECT max(revision)
                FROM answers a2
                WHERE a1.assessmentIdentity = a2.assessmentIdentity
                AND   a1.componentIdentity  = a2.componentIdentity
                AND   a1.modelIdentity      = a2.modelIdentity
            )
                
            """ % (assessment.getIdentity(), assessment.getModelIdentity(), attributeHandle.getAttributeIdentity())

      
        #do we have any sql to perform for this attribute?
        if sql is not None:

            #get a database connection to work with (if we don't have one)
            if dbConnection is None:
                dbConnection = self.getDatabaseConnection()
        
            #log the sql so we can see what is going on
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

            #execute the query 
            (columns, resultset) = dbConnection.query(sql)

            #did we find a result?
            if len(resultset) > 0:
                #reconstruct the attribute from the record
                values = resultset[0]

                if valueType == st_BOOLEAN:
                    assessmentAttribute = AssessmentAttribute(attributeHandle, assessment.getIdentity(), iif(values[5]==1, true, iif(values[5]==0, false, undefined)))

                elif valueType == st_FLOAT:
                    assessmentAttribute = AssessmentAttribute(attributeHandle, assessment.getIdentity(), float(values[5]))

                elif valueType == st_INTEGER:
                    assessmentAttribute = AssessmentAttribute(attributeHandle, assessment.getIdentity(), int(values[5]))

                elif valueType == st_STRING:
                    assessmentAttribute = AssessmentAttribute(attributeHandle, assessment.getIdentity(), values[5])

                assessmentAttribute.setInstantCreated(values[3])
                assessmentAttribute.setInstantModified(values[4])                    
                assessmentAttribute.setIsNew(false)
                assessmentAttribute.setIsModified(false)
                return assessmentAttribute                

            else:
                return None
        
    #---------------------------------------------------------------#

    security.declarePublic('existsAttribute')
    def existsAttribute(self, assessment, attributeHandle, dbConnection = None):
        """
            Returns if persistent attribute state exists for the
            specified attribute in the provided assessment.
            
            NOTE: This method checks the underlying persistent storage
            for the specified attributes's state.  It does not check 
            the 'cached' assessment for the attribute.  That is,
            this method will return 'false' if the underlying 
            persistent storage does not contain the attribute
            even if the provided assessment contains the component
            state.
            
            RETURN TYPE: Boolean
        """            

        #determine the type of the attribute
        valueType = attributeHandle.getValueType()

        #sql to find the component state
        sql = """
            SELECT * FROM answers a1
            WHERE assessmentIdentity = '%s' 
            AND   modelIdentity = '%s' 
            AND   componentIdentity = '%s'
            AND   revision = (
                SELECT max(revision)
                FROM assessments a2
                WHERE a1.assessmentIdentity = a2.assessmentIdentity
                AND   a1.componentIdentity  = a2.componentIdentity
                AND   a1.modelIdentity      = a2.modelIdentity
            )
                
            """ % (table, assessment.getIdentity(), assessment.getModelIdentity(), attributeHandle.getAttributeIdentity())

        #get a database connection to work with (if we don't have one)
        if dbConnection is None:
            dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #did we find the attribute?
        (columns, resultset) = dbConnection.query(sql)
        return len(resultset) > 0
        
    #---------------------------------------------------------------#

    security.declarePrivate('deleteAllAttributes')
    def deleteAllAttributes(self, attributeHandle):
        """
            Deletes all of the attributes with the 
            specified attributeIdentity.
            
            This method is called when a GenericObject is deleted 
            (that uses attributes) to clean up the assessment storage
        """
        
        #determine the type of the attribute
        valueType = attributeHandle.getValueType()

        #sql to delete the attribute
        sql = "DELETE FROM answers WHERE modelIdentity = '%s' and componentIdentity = '%s'" % ( attributeHandle.getModelIdentity(), attributeHandle.getAttributeIdentity())

        #get a database connection to work with (if we don't have one)
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #delete the attribute
        dbConnection.query(sql)
            
    #---------------------------------------------------------------#

    security.declarePrivate('saveAttribute')
    def saveAttribute(self, attribute, instantModified = None, dbConnection = None):
        """
            Stores the specified assessment attribute
            
            instantModified (optional) a DateTime instance that 
            is used to force the modification time of the attribute
            to a specified value
            
            (generally called by self.saveAssessment)
        """
        
        #get the type of the attribute
        valueType = attribute.getAttributeHandle().getValueType()
    
        #let's assume we don't have to do any db work (we are very lazy now.  we only hit the db if we have too)
        sql = None
        
        #do we have a modification time?
        if instantModified is None:
            instantModified = DateTime()
        
        #set the new modification time if this component isn't new
        if not attribute.getIsNew() and attribute.getIsModified():
            attribute.setInstantModified(instantModified)
        
        #get the sql creation and modified time instants 
        instantCreated  = attribute.getInstantCreated().strftime(DATETIME_FORMAT)
        instantModified = attribute.getInstantModified().strftime(DATETIME_FORMAT)

        #determine the query based on the attribute value type
        if valueType == st_BOOLEAN:
            #determine the persistent value
            value = iif(attribute.getValue() == true, 1, iif(attribute.getValue() == false, 0, -1)) 
        else:
            value = attribute.getValue()        
                    
        
        sql = "REPLACE answers (assessmentIdentity, modelIdentity, componentIdentity, instantCreated, instantModified, value, valueType) VALUES ('%s', '%s', '%s', '%s', '%s', %s, '%s')" % (attribute.getAssessmentIdentity(), attribute.getAttributeHandle().getModelIdentity(), attribute.getAttributeHandle().getAttributeIdentity(), instantCreated, instantModified, qstr(value), valueType)
            


        #do we have any sql to perform on this attribute?
        if sql is not None:
            #get a database connection to work with (if we don't have one)
            if dbConnection is None:
                dbConnection = self.getDatabaseConnection()
        
            #log the sql so we can see what is going on
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

            #execute the query 
            dbConnection.query(sql)

            #the attribute is now stored
            attribute.setIsNew(false)
            attribute.setIsModified(false)
             
    #---------------------------------------------------------------#

    security.declarePrivate('deleteAssessmentsForModel')
    def deleteAssessmentsForModel(self, model):
        """
            Deletes all of the assessment state for 
            the specified model

            (including within repositories)
        """

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()

        #delete the model assessments from the assessments table
        sql = "DELETE FROM assessments WHERE modelIdentity = '%s'" % (model.getIdentity(), )
        dbConnection.query(sql)

        #delete the model assessments from the booleans table
        sql = "DELETE FROM answers WHERE modelIdentity = '%s'" % (model.getIdentity(), )
        dbConnection.query(sql)
        
        #delete the model assessments from the repositories table
        sql = "DELETE FROM repositories WHERE modelIdentity = '%s'" % (model.getIdentity(), )
        dbConnection.query(sql)
        
        self.execute( "DELETE FROM search WHERE modelIdentity = %s" % qstr(model.getIdentity()))
            
    #---------------------------------------------------------------#

    security.declarePrivate('deleteAllAssessments')
    def deleteAllAssessments(self):
        """
            Deletes all of the assessments that are 
            stored using this assessment storage tool.

            (including within repositories)
        """

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()

        #delete the assessments from the assessments table
        dbConnection.query("TRUNCATE assessments")

        #delete the assessments from the booleans table
        dbConnection.query("TRUNCATE answers")
        
        #delete the assessments from the repositories table
        dbConnection.query("TRUNCATE repositories")
        
        dbConnection.query("TRUNCATE search")
                
    #---------------------------------------------------------------#

    def isAssessmentInRepository(self, assessment, repositoryIdentity):
        """
            Returns if the specified assessment is in the
            repository specified by the repository identity.
            
            TODO: MySQL should to the counting here with a COUNT(*)
        """

        #query to find the assessment in the repository
        sql = "SELECT * FROM repositories WHERE assessmentIdentity = '%s' and modelIdentity = '%s' and repositoryIdentity = '%s'" % (assessment.getIdentity(), assessment.getModelIdentity(), repositoryIdentity)

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #did we find any state?
        (columns, resultset) = dbConnection.query(sql)
        return len(resultset) > 0

    #---------------------------------------------------------------#

    security.declarePublic('addAssessmentToRepository')
    def addAssessmentToRepository(self, assessment, repositoryIdentity):
        """
            Adds the specified assessment to the
            repository with the specified identity.  If the assessment 
            is already in the repository, nothing happens.
        """

        if not self.isAssessmentInRepository(assessment, repositoryIdentity):
        
            #generate a joining time
            instantJoined  = DateTime().strftime(DATETIME_FORMAT)
        
            #sql to insert the assessment into the specified repository
            sql = "INSERT INTO repositories (assessmentIdentity, modelIdentity, repositoryIdentity, instantJoined) VALUES ('%s', '%s', '%s', '%s')" % (assessment.getIdentity(), assessment.getModelIdentity(), repositoryIdentity, instantJoined) 

            #log the sql so we can see what is going on
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

            #get a database connection to work with 
            dbConnection = self.getDatabaseConnection()
            dbConnection.query(sql)
            
    #---------------------------------------------------------------#

    def removeAssessmentFromRepository(self, assessment, repositoryIdentity):
        """
            Removes the specified assessment from the repository
            with the specified identity.  If the assessment is not
            in the repository, nothing happens.
        """
        
        #sql to delete the assessment from the specified repository
        sql = "DELETE FROM repositories WHERE assessmentIdentity = '%s' and modelIdentity = '%s' and repositoryIdentity = '%s'" % (assessment.getIdentity(), assessment.getModelIdentity(), repositoryIdentity)

        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #delete the component state
        dbConnection.query(sql)
        
    #---------------------------------------------------------------#

    def removeAssessmentFromRepositories(self, assessment):
        """
            ABSTRACT: Removes the specified assessment from 
            all of the repository in which it is located.
            
            If the assessment is not in a repository, nothing happens.
        """
        
        # SQL to delete the assessment from all of the repositories
        sql = "DELETE FROM repositories WHERE assessmentIdentity = '%s' and modelIdentity = '%s'" % (assessment.getIdentity(), assessment.getModelIdentity())
        self.sqlQuery(sql)
        
    #---------------------------------------------------------------#

    def removeAssessmentsFromRepository(self, model, repositoryIdentity):
        """
            Removes all assessments from the specified model 
            repository.
            
            Does nothing if the repository doesn't exist
        """
        
        # SQL to delete the assessment from the specified repository
        sql = "DELETE FROM repositories WHERE modelIdentity = '%s' and repositoryIdentity = '%s'" % (model.getIdentity(), repositoryIdentity)
        self.sqlQuery(sql)
        
    #---------------------------------------------------------------#

    security.declarePublic('getInstantAssessmentJoinedRepository')
    def getInstantAssessmentJoinedRepository(self, assessment, repositoryIdentity):
        """
            Return the instant in time (since the Epoc) when the assessment
            was added to the specified respository
            
            RETURNS: -1 if the assessment is not in the respository
            
            RETURN TYPE: int
        """ 

        #query to find the assessment in the repository
        sql = "SELECT instantJoined FROM repositories WHERE assessmentIdentity = '%s' and modelIdentity = '%s' and repositoryIdentity = '%s'" % (assessment.getIdentity(), assessment.getModelIdentity(), repositoryIdentity)

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #did we find any state?
        (columns, resultset) = dbConnection.query(sql)
        if len(resultset) > 0:
            return resultset[0][0]
        else:
            return -1
        
    #---------------------------------------------------------------#

    security.declarePublic('getRepositoriesForAssessment')
    def getRepositoriesForAssessment(self, assessment):
        """
            Returns a list of repositoryIdentities
            in which the specified assessment is located.
        """
        #query to find the assessment in the repository
        sql = "SELECT repositoryIdentity FROM repositories WHERE assessmentIdentity = '%s'" % (assessment.getIdentity(), )

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #return the respositoryIdentities
        (columns, resultset) = dbConnection.query(sql)
        return [row[0] for row in resultset]
        
    #---------------------------------------------------------------#
    
    security.declarePublic('getNrAssessmentsInRepository')
    def getNrAssessmentsInRepository(self, model, repositoryIdentity):
        """
            Returns the number of assessments 
            for the specified repository
        """
        
        #query to count the number of assessments for a model
        sql = "SELECT count(assessmentIdentity) FROM repositories WHERE modelIdentity = '%s' AND repositoryIdentity = '%s'" % (model.getIdentity(), repositoryIdentity)

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #return the results
        (columns, resultset) = dbConnection.query(sql)        
        return resultset[0][0]
        
    #---------------------------------------------------------------#

    security.declarePublic('getAssessmentsInRepository')
    def getAssessmentsInRepository(self, model, repositoryIdentity, batchNr = None, batchSize = 30, sortBy = None):
        """
            Returns a list of [Assessment] 
            in the specified model and repository.
            
            NOTE: If a batch number is specified, only the specified
            'batchSize' number of assessments are returned.  Otherwise
            all repository assessments are returned.

            RETURN TYPE: [Assessment]
        """
        
        #query to find the assessments in the repository
        sql = "SELECT repositories.assessmentIdentity, assessments.latestRevision FROM repositories INNER JOIN assessments ON repositories.assessmentIdentity = assessments.assessmentIdentity WHERE repositories.repositoryIdentity='%s' and assessments.modelIdentity='%s'" % (repositoryIdentity, model.getIdentity())
        
        # Allow the assessment to be sorted by the repository or model level sort settingss
        repository = self.getComponentByIdentity( repositoryIdentity )
        assessmentSortOrder   = (repository and repository.getAssessmentSortOrder())   or model.getAssessmentSortOrder()
        assessmentSortReverse = (repository and repository.getAssessmentSortReverse()) or model.getAssessmentSortReverse()
        
        if assessmentSortOrder and assessmentSortOrder != "None":
            if assessmentSortReverse:
                sortMode = "DESC"
            else:
                sortMode = "ASC"
            
            sql += "ORDER BY assessments.%s %s " % (qstr(assessmentSortOrder, quote=False), sortMode)
                    
        #do we have a batch size?
        if batchNr is not None:
            sql = sql + (" LIMIT %d, %d" % (int(batchNr * batchSize), batchSize))

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #get assessments
        (columns, resultset) = dbConnection.query(sql, max_rows=0)        
        assessments = []
        for row in resultset:
            #get the assessment identity to load        
            assessmentIdentity = row[0]
            latestRevision     = row[1] or 1
            
            
            
            #load the assessment
            assessment = self.loadAssessment(assessmentIdentity, model, revision=latestRevision)
            
            #add the assessment to the result (if it is known)
            if assessment is not None:
                assessments.append(assessment)
        
        return assessments
        
    #---------------------------------------------------------------#

    security.declarePublic('getAssessmentsInRepositoryWithScope')
    def getAssessmentsInRepositoryWithScope(self, model, repositoryIdentity, scope, authenticatedUserIdentity):
        """
            Returns the assessments for the specified repository with in the specified scope, possibly
            using the authenticatedUser object (User)
            
            Returns a list of assessments that have been created by users that are in the specified
            scope list that are also in the specified repository.
            
            Parameters:
            scope : [String] - Is a list of actorIdentities that created the assessments
            authenticatedUserIdentity : String - Is a knowledge engine user
        """

        #do we have a scope?
        if scope is None or scope == []:
            return []

        #are we after all assessments?
        elif '*' in scope:
            return self.getAssessmentsInRepository(model, repositoryIdentity)
            
        #we are after a specific set of assessments
        else:
            #TODO: This might be an inefficient way to search, but it works for now!
            #We may want to rearrange it in the first instance to look like this;
            #SELECT assessments.assessmentIdentity 
            #FROM assessments 
            #WHERE assessments.assessmentIdentity IN (SELECT repositories.assessmentIdentity FROM repositories WHERE repositories.repositoryIdentity = <repositoryIdentity>) 
            #      AND assessments.createdBy IN <userSet>
            
            #build a set of users to check for assessment ownership
            #(replace the 'authenticateduser' with the authenicatedUser identity)
            #(replace the 'authenticatedusergroup' with the users in the authenticated user's group)
            #(replace the 'authenticatedusermanagedgroup' with the users in the groups that authenticated user is managing)
            userSet = ""
            for actorIdentity in scope:
                if actorIdentity == 'authenticateduser':
                    if len(userSet) > 0:
                        userSet = userSet + ","
                    userSet = userSet + ("'%s'" % authenticatedUserIdentity)
                    
                elif actorIdentity in ('authenticatedusergroup','authenticatedusermanagedgroup','authenticateduseraccessiblegroup'):
                    #get the group provider service so we can determine the members of the group
                    groupProviderService = model.getGroupProviderService()
                    
                    #determine the groups from which to derive users                    
                    if actorIdentity == 'authenticatedusergroup':
                        #get the groups to which the user belongs
                        groups = groupProviderService.getGroupsForActor(authenticatedUserIdentity)                        
                    elif actorIdentity == 'authenticateduseraccessiblegroup':
                        groups = groupProviderService.getGroupsAccessibleByActor(authenticatedUserIdentity)
                    elif actorIdentity == 'authenticatedusermanagedgroup':                    
                        #get the groups that the actor is managing
                        groups = groupProviderService.getGroupsManagedByActor(authenticatedUserIdentity)
                    
                    #add all of the users in each of the groups to the user set
                    for group in filter(None,groups):
                        for userIdentity in groupProviderService.getUserIdentitiesInGroup(group.getIdentity()):
                            if len(userSet) > 0:
                                userSet = userSet + ","
                            userSet = userSet + ("'%s'" % userIdentity)
                                
                else:
                    if len(userSet) > 0:
                        userSet = userSet + ","
                    userSet = userSet + ("'%s'" % actorIdentity)
        
            #we only execute the query if we have a user set
            if len(userSet) > 0:
                #query to find assessments the assessments repository
                
                repository = self.getComponentByIdentity( repositoryIdentity )
                assessmentSortOrder = repository.getAssessmentSortOrder() or model.getAssessmentSortOrder()
                
                sql = """
                    SELECT repositories.assessmentIdentity 
                    FROM repositories 
                    LEFT JOIN assessments ON repositories.assessmentIdentity = assessments.assessmentIdentity 
                    WHERE assessments.modelIdentity = '%s' 
                    AND repositories.repositoryIdentity = '%s' 
                    AND (assessments.createdBy IN (%s) OR assessments.responsible IN (%s))
                    """ % (model.getIdentity(), repositoryIdentity, userSet, userSet)
                    
                # Allow the assessment to be sorted by the repository or model level sort settingss
                repository = self.getComponentByIdentity( repositoryIdentity )
                assessmentSortOrder   = (repository and repository.getAssessmentSortOrder())   or model.getAssessmentSortOrder()
                assessmentSortReverse = (repository and repository.getAssessmentSortReverse()) or model.getAssessmentSortReverse()
        
                if assessmentSortOrder and assessmentSortOrder != "None":
                    if assessmentSortReverse: sortMode = "DESC"
                    else: sortMode = "ASC"
                    
                    sql += "ORDER BY assessments.%s %s" % (qstr(assessmentSortOrder, quote=False), sortMode)

                #get a database connection to work with 
                dbConnection = self.getDatabaseConnection()
        
                #log the sql so we can see what is going on
                log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

                #get assessments
                (columns, resultset) = dbConnection.query(sql, max_rows=0)        
                assessments = []
                for row in resultset:
                    #get the assessment identity to load        
                    assessmentIdentity = row[0]

                    #load the assessment
                    assessment = self.loadAssessment(assessmentIdentity, model)

                    #add the assessment to the result (if it is known)
                    if assessment is not None:
                        assessments.append(assessment)
            else:
                #as there are no users in scope, there can be no assessments
                assessments = []
                
            return assessments
        
    #---------------------------------------------------------------#

    def isAssessmentInScope(self, assessment, scope, authenticatedUserIdentity):
        """
            Returns if the specified assessment is in the specified scope, possibly
            using the authenticatedUser object (User)

            RETURNS: Boolean
            
            Parameters:
            assessment : Assessment - An Assessment
            scope : [String] - Is a list of actorIdentities that created the assessments
            authenticatedUserIdentity : String - Is a knowledge engine user
        """

        #do we have a scope?
        if scope is None or scope == []:
            return false

        #are we after all assessments?
        elif '*' in scope:
            return true
            
        #we are after a specific set of assessments
        else:
            #TODO: This might be an inefficient way to search, but it works for now.  
            #SELECT COUNT(assessments.assessmentIdentity)
            #FROM assessments 
            #WHERE assessments.assessmentIdentity IN (SELECT repositories.assessmentIdentity FROM repositories WHERE repositories.repositoryIdentity = <repositoryIdentity>) 
            #      AND assessments.createdBy IN <userSet>
            
            #build a set of users to check for assessment ownership
            #(replace the 'authenticateduser' with the authenicatedUser identity)
            #(replace the 'authenticatedusergroup' with the users in the authenticated user's group)
            #(replace the 'authenticatedusermanagedgroup' with the users in the groups that authenticated user is managing)
            userSet = []
            for actorIdentity in scope:
                if actorIdentity == 'authenticateduser':
                    userSet.append(authenticatedUserIdentity)
                    #userSet = userSet + ("'%s'" % authenticatedUserIdentity)
                    
                    
                elif actorIdentity in ('authenticatedusergroup', 'authenticatedusermanagedgroup','authenticateduseraccessiblegroup'):
                    #get the group provider service so we can determine the members of the group
                    groupProviderService = assessment.getModel().getGroupProviderService()
                    
                    #determine the groups from which to derive users                    
                    if actorIdentity == 'authenticatedusergroup':
                        #get the groups to which the user belongs
                        groups = groupProviderService.getGroupsForActor(authenticatedUserIdentity)
                    
                    elif actorIdentity == 'authenticatedusermanagedgroup':                    
                        #get the groups that the actor is managing
                        groups = groupProviderService.getGroupsManagedByActor(authenticatedUserIdentity)
                    
                    elif actorIdentity == "authenticateduseraccessiblegroup":
                        groups = groupProviderService.getGroupsAccessibleByActor(authenticatedUserIdentity)
                    
                    #add all of the users in each of the groups to the user set
                    for group in groups:
                        for userIdentity in groupProviderService.getUserIdentitiesInGroup(group.getIdentity()):                           
                            #userSet = userSet + ("%s" % userIdentity)
                            userSet.append( userIdentity )
                                
                else:                    
                    userSet.append(actorIdentity)
        
            #we only execute the query if we have a user set
            if len(userSet) > 0:

                #query to find assessments the assessments repository
                sql = """
                    SELECT COUNT(assessmentIdentity) 
                    FROM assessments 
                    WHERE assessments.assessmentIdentity = %s
                    AND assessments.modelIdentity = '%s' 
                    AND (assessments.createdBy IN (%s) OR assessments.responsible IN (%s))
                    
                    """ % (qstr(assessment.getIdentity()), qstr(assessment.getModel().getIdentity()), qlist(userSet), qlist(userSet))

                #get a database connection to work with 
                dbConnection = self.getDatabaseConnection()
        
                #log the sql so we can see what is going on
                log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

                #get assessments
                (columns, resultset) = dbConnection.query(sql, max_rows=0)                    
                return resultset[0][0] > 0
                
            else:
                #as there are no users, there can't be any scope
                return false
                
    #---------------------------------------------------------------#

    security.declarePublic('getNrAssessmentsForModel')
    def getNrAssessmentsForModel(self, model):
        """
            Returns the number of assessments 
            for the specified model (regardless of the repository)
        """
        
        #query to count the number of assessments for a model
        sql = "SELECT count(assessments.assessmentIdentity) FROM assessments WHERE assessments.modelIdentity = '%s'" % model.getIdentity()

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #return the results
        (columns, resultset) = dbConnection.query(sql)        
        return resultset[0][0]
        
    #---------------------------------------------------------------#

    security.declarePublic('getAssessmentsForModel')
    def getAssessmentsForModel(self, model, batchNr = None, batchSize = 30, sortBy = None):
        """
            Returns the assessments for the specified
            model (regardless of the repository)
            
            NOTE: If a batch number is specified, only the specified
            'batchSize' number of assessments are returned.  Otherwise
            all assessments are returned.

            RETURN TYPE: [Assessment]
        """
        
        #query to find all assessments
        sql = "SELECT assessments.assessmentIdentity FROM assessments WHERE assessments.modelIdentity = '%s'" % model.getIdentity()
        
        
        if sortBy:
        
            #what are we sorting by?
            if sortBy == 'isLocked':
                sql = sql + " ORDER BY assessments.isLocked desc, assessments.instantModified desc" 
            elif sortBy == 'instantModified':
                sql = sql + " ORDER BY assessments.instantModified desc" 
            elif sortBy == 'instantCreated':
                sql = sql + " ORDER BY assessments.instantCreated" 
            elif sortBy == 'name':
                sql = sql + " ORDER BY assessments.name" 
        
        else:
            # Allow the assessment to be sorted by the repository or model level sort settingss
            assessmentSortOrder   = model.getAssessmentSortOrder()
            assessmentSortReverse = model.getAssessmentSortReverse()
            
            if assessmentSortOrder and assessmentSortOrder != "None":
                if assessmentSortReverse: sortMode = "DESC"
                else: sortMode = "ASC"
            
                sql += " ORDER BY assessments.%s %s " % (qstr(assessmentSortOrder, quote=False), sortMode)
        
        
        
        #do we have a batch size?
        if batchNr is not None:
            sql = sql + (" LIMIT %d, %d" % (int(batchNr * batchSize), batchSize))

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #get assessments
        (columns, resultset) = dbConnection.query(sql, max_rows=0)        
        assessments = []
        for row in resultset:
            #get the assessment identity to load        
            assessmentIdentity = row[0]
            
            #load the assessment
            assessment = self.loadAssessment(assessmentIdentity, model)
            
            #add the assessment to the result (if it is known)
            if assessment is not None:
                assessments.append(assessment)
        
        return assessments
        
    #---------------------------------------------------------------#

    security.declarePublic('getNrAssessmentsForModelAndUser')
    def getNrAssessmentsForModelAndUser(self, model, user):
        """
            Returns the number of assessments 
            for the specified model and user (regardless of the repository)
        """

        return self.getNrAssessmentsForModelIdentityAndUsername(model.getIdentity(), user.getIdentity())
        
    #---------------------------------------------------------------#

    security.declarePublic('getNrAssessmentsForModelIdentityAndUsername')
    def getNrAssessmentsForModelIdentityAndUsername(self, modelIdentity, userIdentity):
        """
            Returns the number of assessments for model and user identified
            by their respective identities, irrespective of the repository.
        """

        #query to count the number of assessments for a model and user
        sql = """
            SELECT count(assessments.assessmentIdentity) 
            FROM assessments 
            WHERE assessments.modelIdentity = %s 
            AND (assessments.createdBy = %s OR assessments.responsible = %s)
            
            """ % (qstr(modelIdentity), qstr(userIdentity), qstr(userIdentity))
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()

        #return the results
        (columns, resultset) = dbConnection.query(sql)        
        return resultset[0][0]
        
    #---------------------------------------------------------------#

    security.declarePublic('getAssessmentsForModelAndUser')
    def getAssessmentsForModelAndUser(self, model, user, batchNr = None, batchSize = 30, sortBy = None):
        """
            ABSTRACT: Returns the assessments for the specified
            model and user (regardless of the repository)
            
            NOTE: If a batch number is specified, only the specified
            'batchSize' number of assessments are returned.  Otherwise
            all assessments are returned.

            RETURN TYPE: [Assessment]
        """
        
        #query to find the assessments for a model and user
        sql = """
            SELECT assessments.assessmentIdentity 
            FROM assessments 
            WHERE assessments.modelIdentity = %s 
            AND (assessments.createdBy = %s OR assessments.responsible = %s)
            
            """ % (qstr(model.getIdentity()), qstr(user.getIdentity()), qstr(user.getIdentity))

        if sortBy:
        
            #what are we sorting by?
            if sortBy == 'isLocked':
                sql = sql + " ORDER BY assessments.isLocked desc, assessments.instantModified desc" 
            elif sortBy == 'instantModified':
                sql = sql + " ORDER BY assessments.instantModified desc" 
            elif sortBy == 'instantCreated':
                sql = sql + " ORDER BY assessments.instantCreated" 
            elif sortBy == 'name':
                sql = sql + " ORDER BY assessments.name" 
        
        else:
            # Allow the assessment to be sorted by the repository or model level sort settingss
            assessmentSortOrder   = model.getAssessmentSortOrder()
            assessmentSortReverse = model.getAssessmentSortReverse()
            
            if assessmentSortOrder and assessmentSortOrder != "None":
                if assessmentSortReverse: sortMode = "DESC"
                else: sortMode = "ASC"
            
                sql += " ORDER BY assessments.%s %s " % (qstr(assessmentSortOrder, quote=False), sortMode)
        
        #do we have a batch size?
        if batchNr is not None:
            sql = sql + " LIMIT %d, %d" % (int(batchNr * batchSize), batchSize)

        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()
        
        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #get assessments
        (columns, resultset) = dbConnection.query(sql, max_rows=0)        
        assessments = []
        for row in resultset:
            #get the assessment identity to load        
            assessmentIdentity = row[0]
            
            #load the assessment
            assessment = self.loadAssessment(assessmentIdentity, model)
            
            #add the assessment to the result (if it is known)
            if assessment is not None:
                assessments.append(assessment)
        
        return assessments

    #---------------------------------------------------------------#

    security.declarePublic('getTagsForModel')
    def getTagsForModel(self, model):
        """
            Returns all distinct tags for the given model
        """
        
        # SQL = "SELECT DISTINCT(tag) FROM assessmentTags WHERE modelIdentity='%s' ORDER BY tag ASC" % model.getIdentity()
        SQL = """SELECT tag, COUNT(tag) 
                 FROM assessmentTags 
                 INNER JOIN repositories ON repositories.assessmentIdentity = assessmentTags.assessmentIdentity
                 INNER JOIN assessments  ON assessments.assessmentIdentity = repositories.assessmentIdentity
                 WHERE assessmentTags.modelIdentity='%s' 
                 GROUP BY tag
              """ % (model.getIdentity(), )

        # Execute the query
        resultSet = self.execute(SQL)
        
        # Get the tags
        tags = []
        for row in resultSet:
            tags.append({ 'name' : row[0], 'weight' : int(row[1]) })
        
        return tags


    #---------------------------------------------------------------#
    security.declarePublic('removeTagsFromModel')
    def removeTagsFromModel(self, model, tagString):
        """
            Remove tags from model where tags can be a comma separated
            list of tags
        """

        tags = tagString.split(',')
        for tag in tags:
            self.execute("DELETE FROM assessmentTags WHERE modelIdentity='%s' AND tag='%s'" % (model.getIdentity(), tag.strip().lower()))
            

    #---------------------------------------------------------------#
    security.declarePublic('getTagsForAssessment')
    def getTagsForAssessment(self, assessment):
        """
            Retrieve tags for given assessment identity
        """
        
        assessmentIdentity = assessment.getIdentity()
        modelIdentity = assessment.getModelIdentity()
        
        sql = "SELECT tag, COUNT(tag) FROM assessmentTags WHERE assessmentIdentity='%s' AND modelIdentity='%s' GROUP BY tag ORDER BY tag ASC" % (assessmentIdentity, modelIdentity)

        # Execute the query
        resultSet = self.execute(sql)
        
        # Get the tags
        tags = []
        for row in resultSet:
            tags.append(
                {
                    'name'     : row[0],
                    'weight'  : int(row[1])
                }
            )
        
        return tags
        
    #---------------------------------------------------------------#
        
    security.declarePublic('setTagsForAssessment')
    def setTagsForAssessment(self, assessment, tagString, user = None):
        """
        Sets the tags for the assessment
        """
        
        assessmentIdentity = assessment.getIdentity()
        modelIdentity = assessment.getModelIdentity()

        # Split up the tag string by the space character initially
        # TODO: Allow 'tag "another tag" tag tag'
        tags = tagString.split(',')
        
        # Initially split up the tags by space
        if user is not None:
            insertSQL = "REPLACE INTO assessmentTags (assessmentIdentity, modelIdentity, tag, user) VALUES"
        else:
            insertSQL = "REPLACE INTO assessmentTags (assessmentIdentity, modelIdentity, tag) VALUES"
            
        rows = []
        for tag in tags:
            if user is not None:
                rows += ["('%s', '%s', '%s', '%s')" % (assessmentIdentity, modelIdentity, tag.strip().lower(), user.getIdentity())]
            else:
                rows += ["('%s', '%s', '%s')" % (assessmentIdentity, modelIdentity, tag.strip().lower())]

        insertSQL += ",".join(rows)
        
        self.sqlQuery(insertSQL)


    #---------------------------------------------------------------#
    security.declarePublic('removeTagsFromAssessment')
    def removeTagsFromAssessment(self, assessment, tagString, user = None):
        """
            Remove tags from model where tags can be a comma separated
            list of tags
        """

        tags = "','".join([tag.strip().lower() for tag in tagString.split(',')])
        print "removeTagsFromAssessment: %s" % (tags, )
        if tags:
            sql = "DELETE FROM assessmentTags WHERE assessmentIdentity='%s' AND tag IN ('%s')" % (assessment.getIdentity(), tags)
            if user is not None:
                sql += " AND user='%s'" % (user.getIdentity(), )

        self.sqlQuery(sql)
        
    #---------------------------------------------------------------#
        
    def compileKERLtoSQL(self, expression, models, user, join="assessments.assessmentIdentity", depth=1):
        """
            PRIVATE: Attempts to compile a KERL expression (in object representation)
            into SQL.
        
            Returns "" if a where clause can't be generated
        
            RETURN TYPE: String (where clause)
        """
        
        
        #do we have a KERL expression?
        if expression != None:
        
            #get the operator from the expression
            operator = expression[0]

            #-------------------------------------------------------------
            #KERL operator? (any, all, none)
            #(we choose appropriate binary, infix and postfix operators
            if operator == "all":
                bopr = " AND "
                iopr = ""
                popr = ""

            elif operator == "any":
                bopr = " OR "
                iopr = ""
                popr = ""

            elif operator == "none":
                bopr = " AND "
                iopr = "!("
                popr = ")"
            
            
            elif operator == "model":
            
                if expression[1] in KERLOperators.Equal:
                    return "assessments.modelIdentity = %s" % self.qstring( expression[2] )
                else:
                    raise InvalidKerlOperator, expression[1]
            
            

	
            elif operator == "currentuser":
                
                user = self.portal_membership.getAuthenticatedMember()                  
                
                if expression[1] == "responsible":
                    return "assessment.responsible = %s" % self.qstr( user.getId() )
                    
                
                elif expression[1] == "ingroup": # HACKHACk
                                        
                    groupProviderService = self.knowledgeengine_groupproviderservice
                    groupIdentities = [group.getIdentity() for group in groupProviderService.getGroupsForActor(user.getId())]
                    
                    if type(expression[2]) is tuple: # Hack - Tuple in this command denotes 'evaluate expression on rhs'
                        
                        if not groupIdentities:
                            return 'False'
                        
                        exp = expression[2][0]
                        if exp.startswith('idx'):
                            showColumn     = "searchValue"
                            componentCheck = "searchIndex = %s and searchValue != ''" % ( self.qstring(exp[4:]) )
                        else:
                            showColumn     = "value"
                            componentCheck = "componentIdentity = %s" % ( self.qstring(exp) )
                        
                        return "(select %s from latestAnswers where assessmentIdentity = assessments.assessmentIdentity and %s) in (%s)"  \
                                  % ( showColumn, componentCheck, self.qlist( groupIdentities ) )
                    else:
                        return str( expression[2][0] in groupIdentities )
                    
                else:
                    raise expression[1] + " not implemented here"

            #-------------------------------------------------------------
            #assessment operator?
            elif operator == "assessment":
                #createdby operator   
                if len(expression) == 3 and expression[1] == 'createdby':         
                    return "createdby = %s" % qstr(expression[2])
                
                if len(expression) == 3 and expression[1] == 'responsible':         
                    return "responsible = %s" % qstr(expression[2])               

                elif len(expression) == 3 and expression[1] == 'createdbygroup':
                    checkGroup = expression[2]
                    
                    groupProviderService = self.knowledgeengine_groupproviderservice            
                    userIdentities = groupProviderService.getUserIdentitiesInGroup( checkGroup )
                    
                    if userIdentities:
                        quserIdentities = [qstr(uid) for uid in userIdentities]                    
                        return "createdby in (%s)" % ','.join(quserIdentities)
                    else:
                        return "0 -- No users in group %s" % checkGroup
                    
                # 20060530 [JPM]    ischeckedout operator added
                elif len(expression) == 2 and expression[1] == 'islocked':
                    return "assessments.isLocked=1";

                elif len(expression) == 2 and expression[1] == 'isnotlocked':
                    return "assessments.isLocked=0";

                #ischeckedout operator   
                elif len(expression) == 2 and expression[1] == 'ischeckedout':         
                    return "assessments.checkedOutBy IS NOT NULL";
                
                elif len(expression) == 3 and expression[1] == 'inrepository':
                    return "assessments.assessmentIdentity IN (SELECT repositories.assessmentIdentity FROM repositories WHERE repositories.repositoryIdentity = '%s')" % (expression[2],)                    

                elif len(expression) == 3 and expression[1] == 'notinrepository':
                    #TODO: not supported yet
                    return ""

                elif len(expression) == 3 and expression[1] == 'onlyinrepository':                
                    #TODO: not supported yet
                    return ""

                elif len(expression) == 2 and expression[1] == 'onlyincurrentuserrepository':         
                    #TODO: not supported yet
                    return ""

                elif len(expression) == 2 and expression[1] == 'isorphaned':         
                    return "NOT EXISTS (SELECT * FROM repositories WHERE repositories.assessmentIdentity = assessments.assessmentIdentity)"

                #date operators
                elif len(expression) == 3 and expression[1] in ['createdbefore','createdafter','modifiedbefore','modifiedafter']:
                
                    #create a DateTime for the date
                    when = DateTime(expression[2])
                    
                    if expression[1] == 'createdbefore':
                        return "instantCreated < '%s'" % when.strftime(DATETIME_FORMAT)
                    elif expression[1] == 'createdafter':
                        return "instantCreated >= '%s'" % when.strftime(DATETIME_FORMAT)
                    if expression[1] == 'modifiedbefore':
                        return "instantModified < '%s'" % when.strftime(DATETIME_FORMAT)
                    elif expression[1] == 'modifiedafter':
                        return "instantModified >= '%s'" % when.strftime(DATETIME_FORMAT)
                    else:
                        #TODO: there is some problem with the expression.  we should throw an exception
                        return ""
                    
                else:
                    #TODO: there is some problem with the expression.  it's not supported!
                    return ""
            
            #-------------------------------------------------------------
            #a component operator?
            elif expression[0] == 'anycomponent':
                
                modelIdentities = [qstr(model.getIdentity()) for model in models]
                
                opr = expression[1]
                
                if opr == "equals": opr = "="
                if opr == "doesntequal": opr = "!="
                if opr == "contains": opr = "LIKE"
                
                assert(opr in '=', '!=', 'LIKE'), "Invalid anycomponent operator"
                value = expression[2]
                
                if opr == 'LIKE':
                    value = "%%%s%%" % value
                
               
                #generate the SQL where clause component
                return """
                    assessments.assessmentIdentity IN (
                        SELECT assessmentIdentity
                        FROM latestAnswers
                        WHERE modelIdentity in (%s)
                        AND   value %s %s
                    )
    
                        """ % (','.join(modelIdentities), opr, qstr(value))
                
            
            
            elif isComponentOperator(expression):
                #get the component 
                componentIdentity = expression[0]

                # FIXME- Hacked in searchIndex support
                # Better synax would be ['idx',$NAME] ?
                if componentIdentity.startswith('idx:'):
                    
                    #print "Search index query: %s" % `expression`
                    
                    idx   = expression[0][4:]
                    opr   = expression[1]
                    value = expression[2]
                    
                    # HACKHACK: Rough prototype of cross-assessment (Many-Many) searching
                    if opr == 'in':

                        subquery = self.compileKERLtoSQL( value, models, user, 
                                                      join="searchValue", depth=depth+1)
                                                      
                        # Append to table alias to refer to parent query in subqueries.
                        sdepth = depth-1            
                        
                        # Root query doesn't have a postfix
                        if not sdepth: sdepth=""
                        
                                                      
                        cond = """
                            AND modelIdentity = latestAnswers%s.modelIdentity
                            AND assessmentIdentity = latestAnswers%s.assessmentIdentity
                            AND %s
                        """ % (sdepth, sdepth, subquery)
                                                                          
                        
                                                      
                                                      
                    else:
                        value = qstr(value)
                        cond = "AND searchValue %s %s" % (opr, value)
                    
                    return """
                        %s in (
                            SELECT assessmentIdentity
                            FROM latestAnswers as latestAnswers%d
                            WHERE searchIndex = %s
                            %s  
                        )
                    """ % (join, depth, qstr(idx), cond)
                    
                else:                
                    component = models[0].getComponent(componentIdentity)
                    modelIdentities = [qstr(model.getIdentity()) for model in models]
                    
                    #component = model.getComponent(componentIdentity)
                    
                    #handle each component as required
                    #(we don't delegate this here based on component as it is storage specific!)
                    #-------------------------------------
                    if component.isInstanceOf("CheckBox"):
    
                        #determine the value we are looking for                    
                        if expression[1] == "selected":
                            value = true
                            #negate = ""
                        elif expression[1] == "unselected":
                            value = false
                            #negate = ' not '
                        elif expression[1] == "notattempted":                            
                            sql = "%s NOT IN ( select distinct assessmentIdentity FROM latestAnswers WHERE modelIdentity in (%s) AND componentIdentity = '%s' )" \
                                    % (join, ','.join( modelIdentities ), component.getIdentity() )
                                    
                            return sql 

                        else:
                            value = undefined
                            #negate = ' not '
    
                        
    
                        #generate the SQL where clause component
                        return """
                            %s IN (
                                SELECT assessmentIdentity
                                FROM latestAnswers
                                WHERE modelIdentity in (%s)
                                AND   componentIdentity = '%s'
                                AND   value = %s
                            )

                             """ % (join, ','.join(modelIdentities), component.getIdentity(), value)
    
                    #-------------------------------------
                    
                    elif component.isInstanceOf("Selection"):
                        
                        options = component.getChildrenByInstanceOf("Option")
                        optionIdentities = [option.getIdentity() for option in options]
                        
                        if expression[1] == "notattempted":
                            negate = "NOT"
                        elif expression[1] == "attempted":
                            negate = ""
                        else:
                            raise InvalidKerlOperator, expression[1]
                                                
                        return "%s %s IN (select distinct assessmentIdentity FROM latestAnswers WHERE modelIdentity in (%s) AND componentIdentity in (%s))" % \
                                   (join, negate, ','.join( modelIdentities ), qlist( optionIdentities ))
                    
                    #-------------------------------------
                    
                    
                    elif component.isInstanceOf("Option"):
    
                        # 2007-06-13 jamesd - Trac #916 - kerlFilter not handling unanswered options as 'unselected'
                        # Previously we were checking it the component value was equal to TRUE or FALSE.
                        # However, when checking for 'unselected', this won't always match as no value is in the database for new assessments
                        # In this case, we need to change to find the set of assessments that are not in the set marked as True
    
                        #determine the value we are looking for                    
                        if expression[1] == "selected":
                            #value = true
                            negate = ""
                        elif expression[1] == "unselected":
                            #value = false
                            negate = " not "
                        else:
                            #value = undefined
                            negate = " not "
    
                        # generate the SQL where clause component
                        # This subquery will find values that are selected as True. If 'unselected' is specified, this will negate the whole result. (See Trac #916)
                        return """
                            %s %s IN (
                                SELECT assessmentIdentity FROM latestAnswers 
                                WHERE modelIdentity in (%s) 
                                AND componentIdentity = '%s' 
                                AND value = '1')
                        """ % (join, negate, ','.join(modelIdentities), component.getIdentity())       
    
                    #-------------------------------------
                    elif component.isInstanceOf("MultipleChoice"):
    
                        #determine the value we are looking for                    
                        
                        if expression[1] == "notattempted":
                            sql = "%s NOT IN ( select distinct assessmentIdentity FROM latestAnswers WHERE modelIdentity in (%s) AND componentIdentity = '%s' )" \
                                    % (join, ','.join( modelIdentities ), component.getIdentity() )
                                    
                            return sql 
                        
                        elif expression[1] == "selected":
                            value = expression[2]
                            opr = "="
                            
                        #expression[1] == "unselected":
                        else:
                            value = expression[2]
                            opr = "<>"

                        #generate the SQL where clause component
                        return "%s IN (SELECT assessmentIdentity FROM latestAnswers WHERE modelIdentity in (%s) AND componentIdentity = '%s' AND value %s '%s')" % (join, ','.join(modelIdentities), component.getIdentity(), opr, value)                    
                    
                    elif component.isInstanceOf("TextBox") \
                         or component.isInstanceOf("PythonScript") \
                         or component.isInstanceOf("Lookup"):
                        
                        operator = expression[1]
                        value    = expression[2]
                        
                        # FIXME: Security hole- operator is not checked!
                        return """%s IN (
                                    SELECT assessmentIdentity 
                                    FROM answers
                                    WHERE componentIdentity = %s
                                    AND value %s %s
                                  )
                               """ % (join, qstr(component.getIdentity()),operator, qstr(value))
                    
                    else:
                        #TODO: unknown/unsupported component type
                        raise ComponentTypeException, "Unknown component type (%s)" % `component`

            else:
                #unknown type or unsupported type of KERL expression
                return ""
        
            #-------------------------------------------------------------
            #process the KERL operator
            
            #a list of expressions to join
            compiledExpressions = []

            #iterate over each of the sub expressions, converting them to SQL expression as necessary
            for subexpression in expression[1:]:

                #compile the current sub expression
                #(may return "")
                compiledExpression = self.compileKERLtoSQL(subexpression, models, user,join,depth)

                if compiledExpression != "":
                    compiledExpressions = compiledExpressions + [compiledExpression]

            #now build the result SQL from the compiled expressions
            #(don't return anything if we have no compiled expressions)
            if len(compiledExpressions) == 0:
                return ""
                
            #(return the expression itself if there is only one compiled expression)                
            elif len(compiledExpressions) == 1:
                return iopr + compiledExpressions[0] + popr
            
            #many sub expressions
            else:
                result = ""
                for compiledExpression in compiledExpressions:
                    #always add the binary operator after the first compiled expression
                    if result != "":
                        result = result + bopr
                        
                    result = result +  iopr + compiledExpression + popr  

                return "(" + result + ")"
        
        else:
            #no expression means no where clause
            return ""
        
    #---------------------------------------------------------------#

    def compileScopeToSQL(self, scope, models, user):
        """
            Attempts to compile the specified assessment scope
            into an appropriate SQL where clause to be used when filtering
            assessments.
            
            The parameter 'scope' is a list of actor identities (including groups)
            who (or whose members) have created a set of assessments.
            
            Additionally, the following actor identities may be used;
            (these are appropriately substituted when compiling the SQL)
            
            '*'                             : all portal members.
            'authenticateduser'             : the specified user.
            'authenticatedusergroup'        : the members of the specified user's group
            'authenticatedusermanagedgroup' : the members of the groups that the specified user is managing
                
            Specifying a scope of None or [] means None is returned.
            
            RETURNS:
                None    - if no scope was provided
                ""      - if ALL assessments are required
                String  - containing a SQL where clause condition 
                          representing the provide scope
        """
        
        # We don't do anything on the models in this function- we just use it as 
        # as root namespace to call getGroupProviderService on
        model = models[0]
        
        #has any scope been provided?
        if scope is None or len(scope) == 0:
            return None

        #are we after all assessments?
        elif '*' in scope:    
            return ""
            
        #we are after a specific set of assessments
        else:
            #TODO: This might be an inefficient way to search, but it works for now!
            #We may want to rearrange it in the first instance to look like this;
            #SELECT assessments.assessmentIdentity 
            #FROM assessments 
            #WHERE assessments.assessmentIdentity IN (SELECT repositories.assessmentIdentity FROM repositories WHERE repositories.repositoryIdentity = <repositoryIdentity>) 
            #      AND assessments.createdBy IN <userSet>
            
            #build a set of users to check for assessment ownership
            #(replace the 'authenticateduser' with the authenicatedUser identity)
            #(replace the 'authenticatedusergroup' with the users in the authenticated user's group)
            #(replace the 'authenticatedusermanagedgroup' with the users in the groups that authenticated user is managing)
            userSet = []
            for actorIdentity in scope:
                if actorIdentity == 'authenticateduser':
                    userSet.append( user.getIdentity() )
                    
                elif actorIdentity in ('authenticatedusergroup','authenticatedusermanagedgroup','authenticateduseraccessiblegroup'):
                    #get the group provider service so we can determine the members of the group
                    groupProviderService = model.getGroupProviderService()
                    
                    #determine the groups from which to derive users                    
                    if actorIdentity == 'authenticatedusergroup':
                        #get the groups to which the user belongs
                        groups = groupProviderService.getGroupsForActor(user.getIdentity())
                    elif actorIdentity == 'authenticateduseraccessiblegroup':
                        groups = groupProviderService.getGroupsAccessibleByActor(user.getIdentity())
                    elif actorIdentity == 'authenticatedusermanagedgroup':
                        #get the groups that the actor is managing
                        groups = groupProviderService.getGroupsManagedByActor(user.getIdentity())
                    
                    #add all of the users in each of the groups to the user set
                    for group in groups:
                        for userIdentity in groupProviderService.getUserIdentitiesInGroup(group.getIdentity()):
                            userSet.append( userIdentity )                            
                                
                else:                    
                    userSet.append( actorIdentity )
                    #userSet = userSet + ("'%s'" % actorIdentity)

            quserSet = qlist(userSet)

            return "(createdBy IN (%s) OR responsible IN (%s))" % (quserSet, quserSet)
        
    #---------------------------------------------------------------#

    security.declarePublic('getNrAssessmentsSatisfyingfilter')
    def getNrAssessmentsSatisfyingFilter(self, assessmentFilter, user):
        """
            Returns the number of assessments that satisfy 
            the provided assessment filter and user
            
            NOTE: Does not take kerl transitivity into account or 
            showif rules that may cause values to be invalid.
            
            PARAMETERS:
            assessmentFilter : AssessmentFilter
            user : User - A Knowledge Engine User instance

            Assessment scoping provided as part of the AssessmentFilter.
            
            RETURN TYPE: NUMBER
        """            

        #get the model
        models = [self.getModel(identity) for identity in assessmentFilter.getModelIdentities()]

        #determine the scope SQL
        scopeWhereClause = self.compileScopeToSQL(assessmentFilter.getAssessmentScope(), models, user)

        #only proceed if we have a scope
        if scopeWhereClause is None:
            return 0
        else:
            #generate the SQL query
            sql = "SELECT COUNT(DISTINCT assessmentIdentity) FROM assessments WHERE 1 "
           
            identities = [qstr(model.getIdentity()) for model in models]        
            
            sql += "AND modelIdentity in (%s) " % ','.join(identities) 
            
            whereClause = self.compileKERLtoSQL(assessmentFilter.getExpression(), models, user)
            if whereClause:
                sql += "AND " + whereClause

            #add the scope where clause (if there is one)
            if len(scopeWhereClause) > 0:
                sql = sql + " AND " + scopeWhereClause            

            
            #get a database connection to work with 
            dbConnection = self.getDatabaseConnection()

            #log the sql so we can see what is going on
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

            #return the results
            starttime = time.clock()
            (columns, resultset) = dbConnection.query(sql)        
            endtime = time.clock()
            log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, "SQL Execution Time: %f" % (endtime-starttime)))        

            return resultset[0][0]
        
    #---------------------------------------------------------------#

    security.declarePublic('getAssessmentsSatisfyingFilter')
    def getAssessmentsSatisfyingFilter(self, assessmentFilter, user, batchNr = None, batchSize = 30, sortBy = None):
        """
            Returns the assessments that satisfy the 
            provided assessment filter and user
            
            NOTE 1: Does not take kerl transitivity into account or 
            showif rules that may cause values to be invalid.
            
            NOTE 2: If a batch number is specified, only the specified
            'batchSize' number of assessments are returned.  Otherwise
            all assessments are returned that satisfy the filter.
            
            Assessment scoping provided as part of the AssessmentFilter.
            
            RETURN TYPE: [Assessment]
        """            

        #get the model
        #model = self.getModel(assessmentFilter.getModelIdentity())
        models = [self.getModel(identity) for identity in assessmentFilter.getModelIdentities()]

        #determine the scope SQL    
        scopeWhereClause = self.compileScopeToSQL(assessmentFilter.getAssessmentScope(), models, user)

        #only proceed if we have a scope
        if scopeWhereClause is None:
            return []
            
        else:
            #generate the SQL query
            sql = """
                SELECT DISTINCT assessmentIdentity 
                FROM assessments 
                LEFT OUTER JOIN latestAnswers USING(assessmentIdentity)
                WHERE 1
                """
            
            identities = [qstr(model.getIdentity()) for model in models]        
            
            sql += "AND assessments.modelIdentity in (%s) " % ','.join(identities)
            
            whereClause = self.compileKERLtoSQL(assessmentFilter.getExpression(), models, user)
            if whereClause:
                sql += "AND " + whereClause
            
           
            #if len(whereClause) > 0:
                #sql = sql + (" WHERE modelIdentity = '%s' AND " % model.getIdentity()) + whereClause
            #else:
                #sql = sql + (" WHERE modelIdentity = '%s'" % model.getIdentity())

            #add the scope where clause (if there is one)
            if len(scopeWhereClause) > 0:
                sql = sql + " AND " + scopeWhereClause            



        if sortBy:
        
            #what are we sorting by?
            if sortBy == 'isLocked':
                sql = sql + " ORDER BY assessments.isLocked desc, assessments.instantModified desc" 
            elif sortBy == 'isOpen':
                sql = sql + " ORDER BY assessments.isLocked asc, assessments.instantModified desc"                 
            elif sortBy == 'instantModified':
                sql = sql + " ORDER BY assessments.instantModified desc" 
            elif sortBy == 'instantCreated':
                sql = sql + " ORDER BY assessments.instantCreated" 
            elif sortBy == 'name': 
                sql = sql + " ORDER BY assessments.name" 
        
        else:
            
            assessmentSortOrder   = model.getAssessmentSortOrder()
            assessmentSortReverse = model.getAssessmentSortReverse()
                
            if assessmentSortOrder and assessmentSortOrder != "None":
                    
                if assessmentSortReverse: sortMode = "DESC"
                else: sortMode = "ASC"
            
                sql += " ORDER BY assessments.%s %s " % (qstr(assessmentSortOrder, quote=False), sortMode)


        #do we have a batch size?
        if batchNr is not None:
            sql = sql + " LIMIT %d, %d" % (int(batchNr * batchSize), batchSize)
        
        
        #get a database connection to work with 
        dbConnection = self.getDatabaseConnection()

        #log the sql so we can see what is going on
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, sql))

        #get assessments
        starttime = time.clock()
        (columns, resultset) = dbConnection.query(sql, max_rows=0)        
        endtime = time.clock()
        log.debug("%s:(%d): %s" % (sys._getframe().f_code.co_name, sys._getframe().f_lineno, "SQL Execution Time: %f" % (endtime-starttime)))        

        assessments = []
        for row in resultset:
            #get the assessment identity to load        
            assessmentIdentity = row[0]

            #load the assessment
            assessment = self.loadAssessment(assessmentIdentity, models)

            #add the assessment to the result (if it is known)
            if assessment is not None:
                assessments.append(assessment)

        return assessments
        
    #---------------------------------------------------------------#
    
    def getWorkflowHistory( self, assessmentIdentity, limit=None ):
        """ Returns a log of all workflow changes """
    
        limitClause = ""
        if limit: limitClause = "LIMIT %d" % int(limit)
    
        sql = "SELECT * FROM workflow WHERE assessmentIdentity = %s ORDER BY id DESC %s" % (qstr(assessmentIdentity), limitClause)
        rs = self.sqlQuery( sql )
        return list(rs)
    
    #---------------------------------------------------------------#
    
    def logWorkflowChange( self, assessment, action, comments, msg, when="NOW()" ):
        """ Logs a workflow change 
            Fields:
                assessment: The assessment the action is being performed on
                action: The action being executed
                comments: User comments
                msg: English language description of the action
                     (Needed for auditing in case the action object is deleted)
                     
                when: Date (Object or String) when the event occured, defaults to NOW() for the current time
        """
        
        from DateTime import DateTime
        
        qassessmentIdentity = qstr(assessment.getIdentity())
        qcomments = qstr(comments)
        qmsg      = qstr(msg)
        
        if action:
            qactionIdentity = qstr(action.getIdentity())
        else:
            qactionIdentity = "''"
        
        if not when is "NOW()":
            when = qstr(DateTime().strftime("%Y-%m-%d %H:%M:%S"))
        
        who = qstr(str( self.REQUEST.AUTHENTICATED_USER ))
        
        sql = """
            INSERT INTO workflow(assessmentIdentity, actionIdentity, msg, comments, `when`, userIdentity)
            VALUES(%s, %s, %s, %s, %s, %s)
            """ % ( qassessmentIdentity, qactionIdentity, qmsg, qcomments, when, who )
            
        insert_id = self.sqlQuery( sql )
        
        return insert_id
                
    #---------------------------------------------------------------#        
        
    def isValueUnique( self, component, assessment, formValue ):
        """ Checks to make sure the specified form value is unique for this component in all assessments """
        
        sql = """
            SELECT count(value) as count
            FROM latestAnswers 
            WHERE assessmentIdentity != %s 
            AND   componentIdentity = %s
            AND   value = %s
            """ % (qstr(assessment.getIdentity()), qstr(component.getIdentity()), qstr(formValue))
        
        rs = self.sqlQuery( sql )
        count = int(rs[0]['count'])
    
        if count > 0 :
            return False
        else:
            return True
    
    #---------------------------------------------------------------#
       
    security.declarePrivate('setComponentSearchIndex')
    def setComponentSearchIndex(self, componentIdentity, newIndex ):
        """ Renames an existing search index on a specific component """
        
        sql = "UPDATE answers SET searchIndex = %s WHERE componentIdentity = %s" % ( self.qstring( newIndex ), self.qstring( componentIdentity ) )         
        print sql
        self.execute( sql )

    
    #---------------------------------------------------------------#
    
    security.declarePrivate('createSearchIndex') 
    def createSearchIndex(self, index ):
        """ Creates a search index with the specified name 
        
            RETURNS: True on success, False on failure
        """
           
        sql = "ALTER TABLE search ADD `%s` VARCHAR(255)" % qstr(index, quote=False)
        
        try:
            self.execute( sql )
        except OperationalError: # Error executing query (i.e. column name exists)
            return False
        
        return True
    
    #---------------------------------------------------------------#
    
    def getLinksToAssessment(self, assessmentIdentity):
        """ Returns all assessments linked to the specified assessment 
            RETURNS: [assessmentIdentity]
        """
    
        sql = "SELECT * FROM latestAnswers WHERE value = %s" % qstr(assessmentIdentity)
        results = self.sqlQuery( sql )
    
        linkedIdentities = {}

        for result in results:
            component = self.getComponentByIdentity( result['componentIdentity'] )
            if component.isInstanceOf("Linkable"):
                linkedIdentities[ result['assessmentIdentity'] ] = True
                
        return linkedIdentities.keys()                
     
    #---------------------------------------------------------------#
    
    def getLinksFromAssessment(self, assessmentIdentity):
        """ Returns all assessments linked to the specified assessment 
            RETURNS: [assessmentIdentity]
        """
    
        assessment = self.loadAssessment( assessmentIdentity )
        model = assessment.getModel()
    
        linkComponents = model.getDescendantsByInstanceOf("Linkable")
        
        if not linkComponents:
            return [] # If there are no linkable components, there are no linked assessments
    
        identities = [component.getIdentity() for component in linkComponents]
    
        sql = """ 
            SELECT * FROM latestAnswers 
            WHERE componentIdentity in (%s) 
            AND assessmentIdentity = %s
            AND value in (select assessmentIdentity from assessments) -- For EntityChoosers, make sure the value is actually 
                                                                      -- Linking to an assessment. 
            """ % (qlist( identities ), qstr(assessmentIdentity))
        
        
        
        results = self.sqlQuery( sql )
    
        linkedIdentities = {}

        for result in results:
            aid = result['value']
            if aid:
                linkedIdentities[ aid ] = True
                
        return linkedIdentities.keys()                
     
    #---------------------------------------------------------------#    
    
    def generateSequentialIdentity(self, identityGroup):
        """ Generates an sequential and unique within 'identityGroup' 
            identityGroup: String
        """
        
        # Insert a record into the sequences table, let mysql generate a new AutoNumber id, and return it.
        # This is guarenteed to be unique (within the identity group) and sequential. 
        unique_id = self.sqlQuery( "INSERT INTO sequences (groupName) VALUES (%s)" % qstr(identityGroup) )
        return unique_id
    
        
#####################################################################
## Class Initialisation
# (none)

# Register Service Information with the Knowledge Engine
registerServiceClass(MySQLFiveAssessmentStorageService)

#####################################################################
